Lets assume "and" is not a stopword, in my language it should not be.

How do I search for it using the contains function?

It is a keyword, but how do I escape the keywordness?

No luck with:

SELECT pc.SearchData, from PageContent pc
where contains(pc.SearchData, 'AND')

(got no search hits) or

SELECT pc.SearchData, from PageContent pc
where contains(pc.SearchData, '[AND]')

(this was giving me an exception)...


If you believe that "AND" should not be considered a stop word by SQL DB Engine while performing full-text index search, you need to configure it accordingly.

From CONTAINS description.

The search for characters in the word or phrase is not case-sensitive. Noise words (or stopwords) (such as a, and, or the) in full-text indexed columns are not stored in the full-text index.

If a noise word is used in a single word search, SQL Server returns an error message indicating that the query contains only noise words.

SQL Server includes a standard list of noise words in the directory \Mssql\Binn\FTERef of each instance of SQL Server.

You need to edit \Mssql\Binn\FTERef location manually to remove AND from the list of stopwords.

Here is MSDN resource: CONTAINS Configure and Manage Stopwords and Stoplists for Full-Text Search

  • But the problem is not only that it is considered a stop word, its also a keyword to the contains function? – Anders Lindén Jun 7 '13 at 14:26
  • Also, when looking at the database in ssms, the Full Text Stoplists node has no sub items. Does that mean there is no stoplist? – Anders Lindén Jun 7 '13 at 14:27
  • Updated my answer. AND is not included in the index at all as being a default stopword. Edit the list of default stopwords for your language manually at specified location and rebuild index. – Stoleg Jun 7 '13 at 14:57
  • Lets assume I can get rid of all stopwords, how do I write a query that searches for "AND"? – Anders Lindén Jun 7 '13 at 21:45
  • You are already using right syntax: contains("AND"). Remember that " - double quotes - define an exact phrase to search for. – Stoleg Jun 8 '13 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.