fts within a single record

I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.I believe that you can do this with Full Text Search. You must, however, have a finite set of columns to search upon. You must also have a primary key for the Full Text Search engine to be able to identify the specific record with matching search criteria.

You must enable install and enable Full Text Search (it is not installed by default). You must then create the Full Text Search process (I cheat like crazy and use the wizard in EM; this is NOT recommended). It is recommended that you use QA and create the processes yourself.

You will need to populate the Full Text catalogue initially and then create a schedule to update the Full Text catalogue periodically.

Regards,

Hugh Scott

Originally posted by wooliewillie
I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.