Search a keyword and retrieving column where it was found?

Question

I have some tables, Ex.: Car(id, NameCar) and Person(id, PersonName, Address, etc)

I need to search for a keyword and say in which columns I found it.

Example: Search for Civic, return Person with id 10 have Civic at Car Name, and at Address Name.

How can I do this with good performance?

Answer 1

make a view that contains all the relevant columns. then query that view.

it would help to show specific data values and expected results.

alternately you cuold use a structure with UNIONs and indicate which union the row comes from.. similar to this:

select name, 1 from person where name like '%civic%'
union
select street,2 from address where street like '%civic%'

..

etc

Answer 2

I'm not sure how the performance rates with other options, but when we needed that type of feature we created a full-text catalog. You can find this feature in SSMS under [DatabaseName] - Storage - Full Text Catalogs. Define which fields you want to index and then let it build. Then you can query against the index.

Here's a good article to supplement my terrible description: http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm

Answer 3

This is the exact article for your requirements:

http://www.mssqltips.com/tip.asp?tip=1522