CONTAINS (Transact-SQL)
Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches. 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. |
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 | | }
Full-text predicates and functions work on a single table, which is implied in the FROM predicate. To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables.
Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
You can use a four-part name in the CONTAINS or FREETEXT predicate to query full-text indexed columns of the target tables on a linked server. To prepare a remote server to receive full-text queries, create a full-text index on the target tables and columns on the remote server and then add the remote server as a linked server.
In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned and their size. Another consideration is that LIKE performs only a simple pattern scan of an entire table. A full-text query, in contrast, is language aware, applying specific transformations at index and query time, such as filtering stopwords and making thesaurus and inflectional expansions. These transformations help full-text queries improve their recall and the final ranking of their results.
You can query multiple columns by specifying a list of columns to search. The columns must be from the same table.
For example, the following CONTAINS query searches for the term "Red" in the Name and Color columns of the Production.Product table of the AdventureWorks2012 sample database.
Use AdventureWorks2012; GO SELECT Name, Color FROM Production.Product WHERE CONTAINS((Name, Color), 'Red');
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>
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
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.
![]() |
---|
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