CONTAINSTABLE function provides a table that comprises zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.
Syntax of CONTAINSTABLE Function :
CONTAINSTABLE ( table ,{ column | * } , ' < contains_search_condition > ' [ ,top_n_by_rank ] )
table is a name of table that has been marked for full-text querying.
column is a name of column to search. Column of type character string are valid full-text searching columns.
*Specifies that all columns in the table that have been registered for full-text searching should be used to search for the given contains search condition(s).
contains_search_condition specifies text to search in column.
top_n_by_rank specifies that only the n highest ranked matches, in descending order, are returned.
Return type of CONTAINSTABLE function is a table.
Examples of CONTAINSTABLE Function :
Example 1 : Use of CONSTAINSTABLE function in select clause
SELECT ContactName, ContactTitle, RANK
FROM Customers C
INNER JOIN CONTAINSTABLE(Customers,ContactName, 'Moreno') AS KEY_TBL
ON C.CustomerID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 100
Output
ContactName ContactTitle RANK
Antonio Moreno Owner 112
Above example returns rank values greater than 100th rank using CONSTAINSTABLE.
To run above query you need to create full-text catalog and full-text index as shown below.
Create fulltext catalog :
CREATE FULLTEXT CATALOG NorthwindCatalog
Create fulltext index :
CREATE FULLTEXT INDEX ON Customers
(ContactName, CompanyName, ContactTitle, Address)
KEY INDEX PK_Customers ON NorthwindCatalog
WITH CHANGE_TRACKING AUTO
In above code PK_Customers is primary key in Customers table.