SQL CONTAINS Predicate

CONTAINS is a predicate in a WHERE clause that performs a full-text search.

CONTAINS searches for a word or phrase using fuzzy logic.

It can search for words, synonyms, words near other words, and more.

Example

#

This query finds products with 'oz' in their package description.

SELECT ProductName, Package 
  FROM Product 
 WHERE CONTAINS(Package, 'oz')
Result:  12 records
ProductName Package
Chang 24 - 12 oz bottles
Chef Anton's Cajun Seasoning 48 - 6 oz jars
Grandma's Boysenberry Spread 12 - 8 oz jars
Northwoods Cranberry Sauce 12 - 12 oz jars
Sasquatch Ale 24 - 12 oz bottles
Steeleye Stout 24 - 12 oz bottles

Using CONTAINS

#

Searching with fuzzy logic means that it can find:

  • A word or phrase.
  • The prefix of a word or phrase.
  • A word near another word.
  • A word inflection, for example, searching for 'go' will find goes, going, went, etc.
  • A synonym, for example, searching for farm animal will find cow, horse, etc.

CONTAINS requires that full text search is enabled in the database.

CONTAINS also requires that a full-text index exists for the searched column.

Full text search is a large topic. For complete details see the Microsoft Docs.

Syntax

Syntax for the CONTAINS function.

CONTAINS (column, string)

column -- the column to search.

string -- the string or expression to search for.


More Examples

CONTAINS. Multiple values.

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Find customers named Paolo, José, or Maria.
 SELECT *
  FROM Customer
 WHERE CONTAINS(FirstName, 'Paolo OR José OR Maria')
Result:  4 records
Id FirstName LastName City Country Phone
1 Maria Anders Berlin Germany 030-0074321
24 Maria Larsson Bräcke Sweden 0695-34 67 21
27 Paolo Accorti Torino Italy 011-4988260
30 José Pedro Freyre Sevilla Spain (95) 555 82 82

CONTAINS. Starting with.

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Find customers whose first names starts with 'Jo'.
SELECT *
  FROM Customer
 WHERE CONTAINS(FirstName, '"Jo*"')
Result:  4 records
Id FirstName LastName City Country Phone
30 José Pedro Freyre Sevilla Spain (95) 555 82 82
43 John Steel Walla Walla USA (509) 555-7969
70 Jonas Bergulfsen Stavern Norway 07-98 92 35
71 Jose Pavarotti Boise USA (208) 555-8097

You may also like



Guides


vsn 3.1