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.
- Enabling full text on the database
- Create the catalog if it does not exist
- Add a full text index on a table
- Add a column to the full text index
- Activate the index
- Start full population
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'MyCatalog','create'
EXEC sp_fulltext_table 'Products', 'create', 'MyCatalog', 'pk_products' EXEC sp_fulltext_table 'Categories', 'create', 'MyCatalog', 'pk_categories'
EXEC sp_fulltext_column 'Products', 'ProductName', 'add' EXEC sp_fulltext_column 'Categories', 'Description', 'add'
EXEC sp_fulltext_table 'Products','activate' EXEC sp_fulltext_table 'Categories','activate'
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 email@example.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