With the CONTAINS predicate, you can search for the following:

  • Words and phrases in text
  • Exact or fuzzy matches
  • Inflectional forms of a word
  • Text in which a search word is close to another search word
  • Synonyms of a searched word
  • A prefix of a word or a phrase only

You can also add your custom weight to words you are searching for. You use the CONTAINS predicate in the WHERE clause of your T-SQL statements.

For all details about this predicate, see the Books Online for SQL Server 2012 article “CONTAINS (Transact-SQL)” at https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql

Here are the most important forms of queries with the CONTAINS predicate in pseudocode, where FTcolumn stands for a full-text indexed column and ‘SearchWord?’ stands for the word or phrase searched:

  • SELECT …FROM…WHERE CONTAINS (FT column, ‘SearchWord1’)
    This is the simplest form. You are searching for rows where the FTcolumn contains an exact match for ‘SearchWord1’. This is a simple term.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘SearchWord1 OR SearchWord2’)
    You are searching for rows where the FTcolumn contains an exact match for ‘Search- Word1’ or for the word ‘SearchWord2’. You can also use AND and AND NOT logical operators and change the order of evaluation of the operators in an expression with parentheses.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘”SearchWord1 SearchWord2”’)
    You are searching for rows where the FTcolumn contains an exact match for the phrase “SearchWord1 SearchWord2.”
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘”SearchWord1*”’) You are searching for rows where the FTcolumn contains at least one word that starts with the letters ‘SearchWord1’.
    This is a prefix term.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘NEAR (SearchWord1,SearchWord2)’)
    You are searching for rows where the FTcolumn contains Search-Word1 and SearchWord2. This is the simplest custom proximity term. In this simplest version, it searches only for occurrences of both words, no matter what the distance and order of terms. The result is similar to a simple term where two words or phrases are connected with the logical AND operator.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘NEAR ((SearchWord1, SearchWord2), distance)’)
    You are searching for rows where the FTcolumn contains SearchWord1 and SearchWord2. The order of the search words is not important; however, the distance is an integer that tells how many nonsearch terms can be maximally between the searched terms in order to qualify a row for the result set.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘NEAR ((SearchWord1, SearchWord2), distance, flag)’)
    You are searching for rows where the FTcolumn contains SearchWord1 and SearchWord2. The two searched terms must be closer together than the distance. The flag can take values TRUE or FALSE; the default is FALSE. If the flag is set to TRUE, then the order of the searched terms is important; Search-Word1 must be in text before SearchWord2.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘FORMSOF(INF LECTI ONA L,SearchWord1)’) This is the generation term format of the predicate. You are searching for the rows where the FTcolumn includes any of the inflectional form of the word SearchWord1.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘FORMSOF(THESAURUS , SearchWord1)’)
    This is again the generation term format of the predicate. You are searching for the rows where the FTcolumn includes either the word SearchWord1 or any of the synonyms for this word defined in the thesaurus file.
  • SELECT …FROM…WHERE CONTAINS (FT column, ‘ISAB OUT (SearchWord1 weight(w1),  SearchWord2 weight(w2))’)
    This is a weighted term. Weights have influence on the rank of the documents returned. However, because the CONTAINS predicate does not rank the results, this form has no influence on it. The weighted form is useful for the CONTAINSTABLE function.
  • SELECT …FROM…WHERE CONTAINS (PR OPERTY (FT column, ‘PropertyName’), ‘SearchWord1’)
    This is a property search. You need to have documents with some known properties. In such a query, you are searching for rows with documents that have the property PropertyName that contain the value SearchWord1.