CONTAINS Predicate

CONTAINS Statement

The CONTAINS predicate provides several text-matching options for building queries. Typically, you use this predicate to query the Contents property— the text contents of a file. You can also query other textual properties with content, such as DocTitle, DocSubject, or a user-defined property. This predicate is an optional part of the optional WHERE clause of the SELECT statement.

1 The below stated query finds all employees with a dept 10 and  that contain the e-name "jhones".

SELECT E-Name, Dept
FROM Employee
WHERE Dept=10
AND CONTAINS(E-Name, 'jhones');
 
2. Using CONTAINS and OR with
The following example returns all category descriptions containing strings with prefixes of either "chain" or "full".
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');

3. Using CONTAINS with
The following example returns all product names that have the word bike near the word performance.

SELECT Description

FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');

4. Using CONTAINS with

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

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

5.Using CONTAINS with A Logical Operator (AND)
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');