>We have some Visual Foxpro tables that we have been converting to SQL Server tables. A few of these VFP tables have a PHD index on them that we use for text searching.
>
>Is there anyway of creating a text search index on a SQL Server table? Is there something built into SQl Server that allows you to text search? Or a third party product.
>
>Thanks,
>
>Jerry
Jerry,
You can use fulltext catalogs and indexes. Their creation differ between version (well SQL2000 ways still work but there is a warning that they're depreceated and might be removed in future versions). Assuming 2000:
-Enable fulltext searching on you database
exec sp_fulltext_database 'enable'
-Create a catalog for fulltext
exec sp_fulltext_catalog 'myCatalog', 'create'
-Create fulltext for table
exec sp_fulltext_table 'myTable', 'create', 'myCatalog', 'myPKIndexName'
-Add columns for fulltext indexing
exec sp_fulltext_column 'myTable', 'myColumn', 'add'
-Activate and populate the index
exec sp_fulltext_table 'myTable', 'activate'
exec sp_fulltext_table 'myTable', 'start_full'
Then your table is ready for fulltext searching. ie: Wth Northwind:
use Northwind
go
exec sp_fulltext_database 'enable'
go
exec sp_fulltext_catalog 'myCatalog','create'
exec sp_fulltext_table 'Customers', 'create', 'myCatalog', 'PK_Customers'
exec sp_fulltext_column 'Customers', 'companyName', 'add'
exec sp_fulltext_table 'Customers', 'activate'
exec sp_fulltext_table 'Customers', 'start_full'
Test selecting with this index:
select * from customers where contains(companyNAme, 'food or box')
select * from customers where contains(companyNAme, 'food near great')
etc
Cetin