Information on the full-text search on the Internet is pretty scarce. Unless you want to read 600 pages long book you are going to have a hard time. We've been toying around with FTS in Jitbit Helpdesk for almost five years now. I wanted to share the code we use.
This is a result of many iterations over the years. The code we've settled with is pretty basic and only covers two use cases, but I believe it's going to be enough for 95% of products out there.
To be clear, I'm not going to be talking about the entire query - you can find enough code snippets on the web. But for some reason, no one talks about how you generate the search query itself. I'm talking about this part
CONTAINS(Body, @ftQuery). Apparently, you can't just throw whatever users enter into
Here is our code:
I'll explain what happens here:
one two "three four"becomes
["one", "two", "three four"]
static FullTextUtils()constructor to see how to get a list of all stop-words.
NEARin between. The above example would look like
one NEAR two NEAR "three four"
That's pretty much it. As you can see it is very basic and I'm by no means an expert in Full-text search. But that code works for us -- it's fast and produces good results.
Let me know how you handle the full-text query generation in the comments.
Max Al Farakh
Max is a co-founder/CTO of Jitbit with tons of experience in customer service.