Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Rowset Functions RSS Feeds

CONTAINSTABLE Function

Posted By: Sarita Patel     Category: SQL Server     Views: 8968

This article explains about CONTAINSTABLE function in sql server with examples.

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.
  
Share: 

 
 

Didn't find what you were looking for? Find more on CONTAINSTABLE Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of CONTAINSTABLE Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!