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
I. Using CONTAINS with A Logical Operator (AND)
The following example uses the ProductDescription table of the AdventureWorks database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word "Aluminum" and the word "spindle." The search condition uses the AND Boolean operator.
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');
GO
J. Using CONTAINS to Verify a Row Insertion
The following example uses CONTAINS within a SELECT subquery. Using the AdventureWorks database, the query obtains the comment value of all the comments in the ProductReview table for a particular cycle. The search condition uses the AND Boolean operator.
USE AdventureWorks;
GO
INSERT INTO Production.ProductReview
(ProductID, ReviewerName, EmailAddress, Rating, Comments)
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5,
'The Mountain-200 Silver from AdventureWorks Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond')
-- Given the full-text catalog for these tables is Adv_ft_ctlg,
-- with change_tracking on so that the full-text indexes are updated automatically.
WAITFOR DELAY '00:00:30'
-- Wait 30 seconds to make sure that the full-text index gets updated.
SELECT r.Comments, p.Name
FROM Production.ProductReview r
JOIN Production.Product p
ON
r.ProductID = p.ProductID
AND r.ProductID = (SELECT ProductID
FROM Production.ProductReview
WHERE CONTAINS (Comments,
' AdventureWorks AND
Redmond AND
"Mountain-200 Silver" '))
GO