How to search using a CONTAINS predicate.

Problem: How to search for a specific text, phrases, synonyms of the same word inside a column.

Solution: CONTAINS

Contains

For doing a full-text search using contains, there are some pre-requisites to have on the database and on the tables, columns you would like to search.

•    Create full text catalog
•  Create full text index on the column.

CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.

CONTAINS can search for:

·         A word or phrase.

·         The prefix of a word or phrase.

·         A word near another word.

·         A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

·         A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

On a side note,

Like can do the same but like when compared with contains is a lot slower as the index scan happens to be different.

Example:

In this article I am going to show you how we can search for a tenses of a verb using contains.

Example 1:

Let’s add few tenses of a verb ‘drive’ and see how contains can search for all the tenses

SELECT [SubjectDescription] 

FROM dbo.subject 

WHERE CONTAINS([SubjectDescription], ' FORMSOF (INFLECTIONAL, drove) '); 

GO 

SELECT [SubjectDescription] 

FROM dbo.subject 

WHERE [SubjectDescription]  like '%drove%'

GO

OUTPUT:

 

The above output shows us the difference between like vs contains. Like could only bring the key word specific and where as contains search results are perfect.