Implementing full text search in T-SQL stored procedures

Here are the steps to implement a full text search in a T-SQL stored procedure.

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
  2. EXEC sp_fulltext_database  'enable' 
    
  3. Create the catalog if it does not exist
  4. EXEC sp_fulltext_catalog   'MyCatalog','create' 
    
  5. Add a full text index on a table
  6. EXEC sp_fulltext_table     'Products', 'create', 'MyCatalog', 'pk_products'
    EXEC sp_fulltext_table     'Categories', 'create', 'MyCatalog', 'pk_categories'
    
  7. Add a column to the full text index
  8. EXEC sp_fulltext_column    'Products', 'ProductName', 'add' 
    EXEC sp_fulltext_column    'Categories', 'Description', 'add'
    
  9. Activate the index
  10. EXEC sp_fulltext_table     'Products','activate'
    EXEC sp_fulltext_table     'Categories','activate'
    
  11. Start full population
  12. 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.

This was first published in June 2002

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close