Implementing full text search in T-SQL stored procedures

Enabling full text search in T-SQL is not as popular as doing it with the Enterprise Manager, but is still worthwhile. Here are the T-SQL steps to take in order to implement FTS in T-SQL.

1. Enabling full text on the database

EXEC sp_fulltext_database  'enable' 

2. Create the catalog if it does not exist

EXEC sp_fulltext_catalog   'MyCatalog','create' 

3. Add a full text index on a table

EXEC sp_fulltext_table     'Products', 'create', 'MyCatalog', 'pk_products'
EXEC sp_fulltext_table     'Categories', 'create', 'MyCatalog', 'pk_categories'

4. Add a column to the full text index

EXEC sp_fulltext_column    'Products', 'ProductName', 'add' 
EXEC sp_fulltext_column    'Categories', 'Description', 'add'

5. Activate the index

EXEC sp_fulltext_table     'Products','activate'
EXEC sp_fulltext_table     'Categories','activate'

6. Start full population

EXEC sp_fulltext_catalog   'MyCatalog', 'start_full'

Using the index in T-SQL:

USE Northwind
GO
SELECT ProductId, ProductName, UnitPrice
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch " OR "stout" ')
GO

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )
GO

For More Information

• What do you think about this tip? E-mail the editor at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.