Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

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



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.