Until now, we have set-up the full text search catalogues
and indexes. Let us run the some queries for the full-text search. There are
four types of predicates used in SQL Server 2005 for running the Full text
search queries. A predicate is an expression that evaluates to TRUE, FALSE or
UNKNOWN. A predicate is used in a where condition in T-SQL statement.
1.
FREETEXT
2.
FREETEXTTABLE
3.
CONTAINS
4.
CONTAINSTABLE
Let us see each one of them individually with its usage.
FREETEXT
Freetext is the simplest form of predicates in the full text
search. It searches for the words that give the similar meaning and not the
exact wording of the word. This kind of predicate is used where we go to query
a document as a whole for a word in it. The freetext not only returns the exact
wording, but also the nearest meanings attached to it. Listing 1 shows the
usage of the free text. To understand listing 1, the user should have a basic
understanding of T-SQL queries. Let us also see the usage of Freetext with a
help of an example. Listing 2 gives an example for the usage of FREETEXT.
Listing 2
FREETEXT ( { column_name | (column_list) | * }
, 'freetext_string' [ , LANGUAGE language_term ] )Listing 3
SELECT CellularPhone FROM CART WHERE FREETEXT (CellularPhone, '1111')
Listing 4
SELECT CELLULARPHONE FROM CART WHERE CELLULARPHONE LIKE '%1111%'
FREETEXTTABLE
Freetexttable returns a collection of one or more rows as a
table. The columns in the rows are selected based on the string given as the
predicate. Freetexttable is similar to freetext except the former can return
tables and the latter one can return columns. Listing 4 shows the usage of
Freetexttable.
Listing 5
FREETEXTTABLE (table , { column_name | (column_list) | * }
, 'freetext_string'
[ ,LANGUAGE language_term ]
[ ,top_n_by_rank ] )CONTAINS
As the name suggests, Contains actually works by verifying
if a word or phrase is contained in the columns. It can search for a word,
prefix of a word, synonym of a word, word formed from another word. We need to
specify the language in case the languages of the words are not of the same
language. Listing 5 shows the simple usage of contains column.
Listing 6
SELECT [Name], [Address]
FROM Customer
WHERE AGE > 30
AND CONTAINS([Name], 'Uday');
CONTAINSTABLE
Contains and Containstable are similar to freetext and
freetexttable. The former one returns columns and the latter one returns the
table.
