Full-Text Search Operators – Part 3: Splitting the search string

Part 1: Cleaning up parentheses in a string
Part 2: Replacing alternate operators

We’ve now got to the point where we have a “clean” search string.  The next step is to split the string into rows which we can then manipulate without having to traverse the string itself.

There are many “split to row” table-valued functions out there, and I’d recommend using a CLR-based one if you can (for performance reasons).  Basically the function accepts an input string and a delimiter, and outputs a table of individual search terms.

Instead of simply inserting these rows into a temporary table or table variable at this stage, I first use a series of CTE’s to further refine the data.

This then feeds into a 2nd CTE which classifies each individual search term:

I wanted the search operators to be case sensitive in order not to change the behaviour of searches where people were including the words “and”, “or”, “not”, or “near”. This means that I needed to specify a case-sensitive collation for the string comparisons (obviously you can remove this if you don’t want case-sensitivity, or you database already uses a case-sensitive collation).

Now another CTE tidies up by removing some superfluous characters, etc.:

Next, remove consecutive & duplicate operators…

…and exclude any empty or unnecessary parentheses…

And lastly, format any wildcard (i.e. prefix) search terms, and insert the results into a table variable:

Phew. Enough for this post I think.

So, at this point if I pass in the following search string – sql NEAR server -(oracle | mysql)then my table variable should be populated with the following:

Table variable contents

In the next (and final) post, I’ll wrap up the “near” operator & double-quote handlers, and the building of the final output string.

See ya!

DB Dave


  1. Pingback: Full-Text Search Operators – Part 1: Cleaning up parentheses in a string - database dave

  2. Pingback: Full-Text Search Operators – Part 4: Wrapping it up - database dave

Leave a Reply