How to generate a full-text query for SQL Server

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 @ftQuery.

Here is our code:

I'll explain what happens here:

1. If the entire query is enclosed in double quotes we don't do anything. The user is looking for an exact match.
2. Split the query by spaces except when there are parts enclosed in double quotes. For example, one two "three four" becomes ["one", "two", "three four"]
3. Go through each array element and remove non-alphabetic symbols with this simple regexp [^\w]
4. Now we actually need to remove stop words from the array. Using our method words like "the", "and", "or", etc. will not be considered as stop-words by SQL. Look at static FullTextUtils() constructor to see how to get a list of all stop-words.
5. At last, you need to join all the words you have and put NEAR in 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.