>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