Integrating Full-Text Search and Transact-SQL Predicates

The CONTAINS and FREETEXT predicates can be combined with any of the other Transact-SQL predicates, such as LIKE and BETWEEN; they can also be used in a subquery. This example searches for descriptions in which the description ID is not equal to 5 and in which the description contains the word "Aluminum" and the word "spindle."

USE AdventureWorks;
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, ' Aluminum AND spindle');

The following query uses CONTAINS within a subquery. Using the AdventureWorks database, the query obtains the comment value of all the comments in the ProductReview table for a particular cycle.

USE AdventureWorks;
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
(780, 'John Smith', '', 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 
 r.ProductID = p.ProductID
AND r.ProductID = (SELECT ProductID
                  FROM Production.ProductReview
                  WHERE CONTAINS (Comments, 
                                 ' AdventureWorks AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '))


Community Additions