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; GO SELECT Description FROM Production.ProductDescription WHERE ProductDescriptionID <> 5 AND CONTAINS(Description, ' Aluminum AND spindle'); GO
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; GO INSERT INTO Production.ProductReview (ProductID, ReviewerName, EmailAddress, Rating, Comments) VALUES (780, 'John Smith', 'firstname.lastname@example.org', 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