Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)...

share|improve this question
up vote 1 down vote accepted

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

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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