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. Moonbeem is well known as the flying saucer capitol 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
| CONTAINS | WHERE |
| FREETEXT |