Full-Text Search Operators – Part 2: Replacing alternate operators

Photo by natashalcdIn this post I’m going to deal with another piece of the puzzle that I described in part 1. Now, after re-reading that post it became obvious that I probably need to outline what the end result is meant to look like:

We want to be able to pass in a search string, and get back a nicely formatted full-text search clause which makes full use of Boolean operators, respects parentheses, and supports wildcards. Easy, right? Yup, and we need to do it all using only T/SQL.

Here’s the steps that make up the final function (in a nutshell):

  1. Remove some dodgy characters (vertical-tab, form-feed, etc)
  2. Tidy up any parentheses [part 1]
  3. Replace any alternate operators with place-holders (most operators have 2 representations; e.g. OR and |, AND and &, NOT and , etc).
  4. Break the search term into individual words (i.e. split the string on space characters)
  5. Using this list of individual words, do the following:
    1. Classify each word as an operator, search term, parenthesis, wildcard, etc.
    2. Tidy up the list, removing illegal and unnecessary characters
    3. Remove invalid operators (e.g. “ipod NOT” is not a valid search term)
    4. Remove unnecessary parentheses (e.g. single words wrapped in parentheses)
    5. Handle wildcards. Since full-text only accepts prefix searches, change all invalid wildcard searches into prefix searches.
  6. Check for single and double-quotes (& tidy up any invalid quote-marks, make sure only valid pairs remain, etc.).
  7. Process any NEAR(~) operators.
  8. Put the final Full-Text command string together.

Right, so we’ve already taken care of number 2 in the previous post, so now on to number 3 – replacing the alternate operators.  This step may not be necessary for you, since it’s sole purpose is to preserve the original search string for our reporting purposes.

You see, if I search for “ipad OR iphone NOT ipod” and you search for “ipad | iphone –ipod”, then obviously since they’re really the same search we will get the same results (and our cache should also cache them as 1 search), but for reporting purposes we want to know that our searches were typed in differently.  The way we do that in our implementation of this function, is that we return a normalised search string used to perform & cache the search, and an original string used for reporting.

Here’s the very straight forward code:

The first bit is very simple; it’s just a REPLACE statement replacing each operator’s “symbol operator” (i.e. |, ~, &) with a bit of placeholder text (i.e. OR, NEAR, AND).

The NOT operators require a little more work because we don’t want to interpret all hyphens as operators, otherwise you wouldn’t be able to search for hyphenated words. Using PATINDEX we can very easily match on a simple RegEx-like pattern: [^a-z0-9][-][ a-z0-9″”(]

This looks for any occurrences of the hyphen, where it follows a non-alphanumeric character, and precedes a space, an alphanumeric character, a double or single quote, or an opening parenthesis. It then replaces each occurrence with the placeholder text NOT.

In the next post I’ll go through the meatiest bit, which involves splitting the string (number 4), and preparing the resulting data (number 5).


DB Dave


  1. Pingback: Full-Text Search Operators – Part 1: Cleaning up parentheses in a string | David Curlewis

  2. Pingback: Full-Text Search Operators – Part 4: Wrapping it up | David Curlewis

  3. Pingback: Full-Text Search Operators – Part 3: Splitting the search string - database dave

Leave a Reply