Getting Started with SQL Server 2005 Full Text Searching: Part 5 – Advanced Searching

Last week we looked at how to search for data by passing a simple search word into a SQL Query that uses Full Text Searching. As you would expect, Full Text Searching is capable of much more powerful searches than a single word.

First, you can search for a phrase. To do so, you include the phrase inside double quotes.

SELECT WebSiteID, WebSiteName
FROM MyTableOfCoolWebsites
WHERE CONTAINS(*, ‘”Arcane Code”’)

Will find occurances of the phrase Arcane Code in your index. Let’s say however, you want to search for either Arcane or Code? FTS supports Boolean searches.

WHERE CONTAINS(*, ‘”Arcane” OR “Code”’)

Will return results where either the word Arcane or Code is in the text. If you want them both, use an AND in place of the OR. Additionally, FTS supports the AND NOT keyword, for when you want the first word but not the second.

Full Text Search also supports something called Proximity searches. In a proximity seach, one word appears near another.

WHERE CONTAINS(*, ‘arcane near code’)

So how near is near? Well the online docs are a bit vague on this, a safe guess is about ten words. You may also see this form of the syntax:

WHERE CONTAINS(*, ‘arcane ~ code’)

~ equates to near, although to me not nearly as readable, be aware though in case you run across it.

Finally, you should be aware that certain words are excluded from searches. Common words such as a, an, the, and so on. Microsoft refers to these as “Noise Words”. You can edit the list of noise words in case you have some words in your environment that wind up being Noise Words. Your company name might be one example.

I found the file in the folder C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\FTData (your milage may vary). The file is named noiseenu.txt. (ENU is for English US, not to be confused with noiseeng.txt which our friends in the British Isles will be using.)

This is also handy to know in case there is a reserverd word you need to remove from the list. In our environment one of the reserve words is also an abbreviation for a piece of our equipment, so I would want to remove this from our list.