CONTAINS is a predicate used in the WHERE clause of a Transact-SQL

CONTAINS (Transact-SQL)

Other Versions

Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. 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").

For information about the forms of full-text searches that are supported by SQL Server, see Query with Full-Text Search.

Applies to: SQL Server (SQL Server 2008 through current version).

 Transact-SQL Syntax Conventions

Syntax

CONTAINS ( 
     { 
        column_name | ( column_list ) 
      | * 
      | PROPERTY ( { column_name }, 'property_name' )  
     } 
     , '<contains_search_condition>'
     [ , LANGUAGE language_term ]
   ) 

<contains_search_condition> ::= 
  { 
      <simple_term> 
    | <prefix_term> 
    | <generation_term> 
    | <generic_proximity_term> 
    | <custom_proximity_term> 
    | <weighted_term> 
    } 
  | 
    { ( <contains_search_condition> ) 
        [ { <AND> | <AND NOT> | <OR> } ] 
        <contains_search_condition> [ ...n ] 
  } 
<simple_term> ::= 
     { word | "phrase" }

<prefix term> ::= 
  { "word*" | "phrase*" }

<generation_term> ::= 
  FORMSOF ( { INFLECTIONAL | THESAURUS } , <simple_term> [ ,...n ] ) 

<generic_proximity_term> ::= 
  { <simple_term> | <prefix_term> } { { { NEAR | ~ } 
     { <simple_term> | <prefix_term> } } [ ...n ] }

<custom_proximity_term> ::= 
  NEAR ( 
     {
        { <simple_term> | <prefix_term> } [ ,…n ]
     |
        ( { <simple_term> | <prefix_term> } [ ,…n ] ) 
      [, <maximum_distance> [, <match_order> ] ]
     }
       ) 

      <maximum_distance> ::= { integer | MAX }
      <match_order> ::= { TRUE | FALSE } 

<weighted_term> ::= 
  ISABOUT 
   ( { 
        { 
          <simple_term> 
        | <prefix_term> 
        | <generation_term> 
        | <proximity_term> 
        } 
      [ WEIGHT ( weight_value ) ] 
      } [ ,...n ] 
   ) 

<AND> ::= 
  { AND | & }

<AND NOT> ::= 
  { AND NOT | &! }

<OR> ::= 
  { OR | | }

Examples






A. Using CONTAINS with <simple_term>


The following example finds all products with a price of $80.99 that contain the word "Mountain".










USE AdventureWorks2012;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain');
GO







B. Using CONTAINS and phrase with <simple_term>


The following example returns all products that contain either the phrase "Mountain" or "Road".










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' Mountain OR Road ')
GO







C. Using CONTAINS with <prefix_term>


The following example returns all product names with at least one word starting with the prefix chain in the Name column.










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO







D. Using CONTAINS and OR with <prefix_term>


The following example returns all category descriptions containing strings with prefixes of either "chain" or "full".










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO







E. Using CONTAINS with <proximity_term>






Applies to: SQL Server 2012 through SQL Server 2014.




The following example searches the Production.ProductReview table for all comments that contain the word "bike" within 10 terms of the word "control" and in the specified order (that is, where "bike" precedes "control").










USE AdventureWorks2012;
GO
SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments , 'NEAR((bike,control), 10, TRUE)');
GO







F. Using CONTAINS with <generation_term>


The following example searches for all products with words of the form ride: "riding," "ridden," and so on.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO







G. Using CONTAINS with <weighted_term>


The following example searches for all product names containing the words performance, comfortable, or smooth, and different weights are given to each word.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8), 
comfortable weight (.4), smooth weight (.2) )' );
GO







H. Using CONTAINS with variables


The following example uses a variable instead of a specific search term.










USE AdventureWorks2012;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);
GO







I. Using CONTAINS with a logical operator (AND)


The following example uses the ProductDescription table of the AdventureWorks2012 database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word Aluminum and the word spindle. The search condition uses the AND Boolean operator.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, 'Aluminum AND spindle');
GO







J. Using CONTAINS to verify a row insertion


The following example uses CONTAINS within a SELECT subquery. Using the AdventureWorks2012 database, the query obtains the comment value of all the comments in the ProductReview table for a particular cycle. The search condition uses the AND Boolean operator.










USE AdventureWorks2012;
GO
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5, 
'The Mountain-200 Silver from AdventureWorks2008 Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond');
 
-- Given the full-text catalog for these tables is Adv_ft_ctlg, 
-- with change_tracking on so that the full-text indexes are updated automatically.
WAITFOR DELAY '00:00:30';   
-- Wait 30 seconds to make sure that the full-text index gets updated.
 
SELECT r.Comments, p.Name
FROM Production.ProductReview r
JOIN Production.Product p 
ON
 r.ProductID = p.ProductID
 
AND r.ProductID = (SELECT ProductID
                  FROM Production.ProductReview
                  WHERE CONTAINS (Comments, 
                                 ' AdventureWorks2008 AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '));

GO







K. Querying on a document property






Applies to: SQL Server 2012 through SQL Server 2014.




The following query searches on an indexed property, Title, in the Document column of the Production.Document table. The query returns only documents whose Title property contains the string Maintenance or Repair.


 Note

For a property-search to return rows, the filter or filters that parse the column during indexing must extract the specified property. Also, the full-text index of the specified table must have been configured to include the property. For more information, see Search Document Properties with Search Property Lists.













Use AdventureWorks2012;
GO
SELECT Document FROM Production.Document
  WHERE CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');
GO