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.
This query finds products with 'oz' in their package description.
SELECT ProductName, Package
FROM Product
WHERE CONTAINS(Package, 'oz')
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 |
Searching with fuzzy logic means that it can find:
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 for the CONTAINS function.
CONTAINS (column, string)
column
-- the column to search.
string
-- the string or expression to search for.
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SELECT *
FROM Customer
WHERE CONTAINS(FirstName, 'Paolo OR José OR Maria')
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 |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SELECT *
FROM Customer
WHERE CONTAINS(FirstName, '"Jo*"')
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 |