Combining Full-text Predicates with Other 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 category is not Seafood, and in which the description contains the word sauces and the word seasonings.

USE Northwind
GO
SELECT Description
FROM Categories
WHERE CategoryName <> 'Seafood' AND
   CONTAINS(Description, ' sauces AND seasonings ')
GO

The following query uses CONTAINS within a subquery. Using the pubs database, the query obtains the title value of all the books in the titles table for the publisher that is located close to the flying saucer in Moonbeam, Ontario. (This information about the publisher is in the pr_info column in the pub_info table, and there is only one such publisher.)

USE pubs
GO
-- Add some interesting rows to some tables.
INSERT INTO publishers 
  VALUES ('9970',
          'Penumbra Press',
          'Moonbeam',
          'ON',
          'Canada')
INSERT INTO pub_info (pub_id, pr_info)
  VALUES ('9970',
          'Penumbra press is located in the small village of Moonbeam.  Moonbeam is well known as the flying saucer capital of Ontario.  You will often find one or more flying saucers docked close to the tourist information centre on the north side of highway 11.')
INSERT INTO titles
  VALUES ('FP0001',
          'Games of the World',
          'crafts',
          '9970',
          9.85,
          0.00,
          20,
          213,
          'A crafts book!  A sports book!  A history book!  The fun and excitement of a world at play - beautifully described and lavishly illustrated',
          '1977/09/15')
GO
-- Given the full-text catalog for these tables is pubs_ft_ctlg, 
-- repopulate it so new rows are included in the full-text indexes.
sp_fulltext_catalog 'pubs_ft_ctlg', 'start_full'
WAITFOR DELAY '00:00:30'   -- Wait 30 seconds for population.
GO
-- Issue the query.
SELECT T.title, P.pub_name
FROM publishers P,
     titles T
WHERE P.pub_id = T.pub_id
  AND P.pub_id = (SELECT pub_id 
                  FROM pub_info
                  WHERE CONTAINS (pr_info, 
                                 ' moonbeam AND 
                                   ontario AND 
                                   "flying saucer" '))
GO

See Also

CONTAINS

FREETEXT

WHERE