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 | *} , “”, ) 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. |