Thanks, this is what I am looking for.
Thanks,
Jerry
>>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