A. Using CONTAINS with <simple_term>
The following example finds all products with a price of $80.99 that contain the word "Mountain".
USE AdventureWorks;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
AND CONTAINS(Name, 'Mountain');
GO
B. Using CONTAINS and phrase in <simple_term>
The following example returns all products that contain either the phrase "Mountain" or "Road".
USE AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO
E. Using CONTAINS with <proximity_term>
The following example returns all product names that have the word bike near the word performance.
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
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 AdventureWorks;
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 weightings are given to each word.
USE AdventureWorks;
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 AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);
GO