This
is new Feature added in SQL Server 2012
CONTAINS
can search for:
·
A word or phrase.
·
The prefix of a word or phrase.
·
A word near another word.
·
A word inflectionally generated from another (for example, the
word drive is the inflectional stem of drives, drove, driving, and driven).
·
A word that is a synonym of another word using a thesaurus (for
example, the word "metal" can have synonyms such as
"aluminum" and "steel").
Examples:
A. Using CONTAINS with <simple_term>
The
following example finds all products with a price of $80.99 that contain the word "Mountain".
USE AdventureWorks2012;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
AND
CONTAINS(Name, 'Mountain');
GO
B. Using CONTAINS and phrase with
<simple_term>
The
following example returns all products that contain either the phrase "Mountain" or "Road".
USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR
"full*"');
GO
E. Using CONTAINS with <proximity_term>
The
following example searches the Production.ProductReview table for all comments that contain
the word "bike"
within 10 terms of the word "control" and in the specified order (that is, where "bike" precedes "control").
USE AdventureWorks2012;
GO
SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments ,
'NEAR((bike,control), 10, TRUE)');
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 AdventureWorks2012;
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 weights are given to each
word.
USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012
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 AdventureWorks2012;
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
AdventureWorks2012 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 AdventureWorks2012;
GO
INSERT INTO Production.ProductReview
(ProductID, ReviewerName, EmailAddress,
Rating, Comments)
VALUES
(780, 'John Smith', 'john@fourthcoffee.com',
5,
'The Mountain-200 Silver from
AdventureWorks2008 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,
' AdventureWorks2008 AND
Redmond AND
"Mountain-200
Silver" '));
GO
K. Querying on a document property
The
following query searches on an indexed property, Title, in the Document column of the Production.Document table. The query
returns only documents whose Title property contains the string Maintenanceor Repair.
Use AdventureWorks2012;
GO
SELECT Document FROM Production.Document
WHERE
CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');
GO
