We take pride in your success. We let our positivity drive us, day in and out. Talk to us at Mindfire to know us more.

Software Technology Tips

This tip demonstrates how to use the primary functions CONTAINSTABLE and CONTAINS, to search in a full text enabled table in MS SQL Server.

1. CONTAINS

CONTAINS is a conditional predicate and used in a where clause to search columns containing character-based data types.

This term looks for a match based on a particular word or phrase which is provided in the sql query.

Basically it looks for an exact match, but it can be extended or modified to look for the inflectional matches.

Basic syntax :

CONTAINS({column | column_list | *} , "<search_conditions>")

In above:

- First argument: can take a single column name or multiple column names or "*" for all columns.

(note that a full text index can be created combining multiple columns of a table)

- Second argument: search_condition, we can put a word or a phrase.

Example:

SELECT Country.Country_Name, Country.Country_Description FROM Country WHERE CONTAINS(Country_Description, 'sea')

The above query results in Country_Name and Country_Description columns where the description column in the index includes the word "sea".

If we want to go for more complex (and thus specific) search conditions then we can do a lot.

CONTAINS with OR/ AND operators:

CONTAINS(Country_Description, ' "sea beach" OR "snow fall" ')

This will return all the records where Country_Description contains the word "sea beach" or "snow fall".

CONTAINS with word prefix:

CONTAINS(Country_Description, ' "eng*" ')

This will return all the records with at least one word starting with the prefix "eng" in the Country_Description column.

CONTAINS with NEAR operator:

CONTAINS(Country_Description, ' "language NEAR english" ')

This looks for the word "language" near "english" in Country_Description column and returns all the matching records.

CONTAINS with generation terms:

CONTAINS(Country_Description, ' FORMSOF (INFLECTIONAL, speak) ')

The above syntax will look for the word which have with different inflectional forms of word speak (like.. spoke, spoken, speaking etc)

CONTAINS with weight values(weight values should be between 0 to 1):

CONTAINS(Country_Description, 'ISABOUT (english weight(.8), franch weight (.4) )' )

In the above syntax, the user can give weight to different search key words.

2. CONTAINSTABLE

CONTAINSTABLE term is useful when we want to sort the resulted search output records by rank.

It is always used in FROM clause, because it returns a temporary table with rank of search result among all the matches found.

If we see the syntax, it is similar to CONTAINS with little twist of

1. identifying which table the CONTAINSTABLE is going to operate against, and

2. an optional limitation to result top n set of matches:

CONTAINSTABLE({column | column_list | *} , "<search_conditions>", <top 'n'>)

CONTAINSTABLE always returns a table while CONTAINS returns a boolean value. So, we can use CONTAINS in where clause but not CONTAINSTABLE.

Resultant table of CONTAINSTABLE will appear something like this:

KEY RANK

=== =====

12 145

16 128

- KEY : Every indexed table in the database should have a column with unique values, and that unique key value should match the key generated by CONTAINSTABLE. So in the above table the KEY column values are the Primary Key column values for the matching entries in the original table.

- RANK : Rank is a value from 0 to 1000, that shows how well the search results matched in the rows being returned. Higher the rank value, better the match.

Searching more than one table using CONTAINSTABLE clause is a complex affair. So it is tobe handled by CONTAINS.

The disadvantage to using CONTAINS is that, it does not return the rank value, so you can not sort results by best match.

Related Tags:
Database

Author: Mritunjay Kumar

Database

More Related Tips

Software Technology Tips

Database

Let us Connect!