On which field was the keyword found? SQL Searching

Question

I'm trying to make a detailed search engine for my web site.
The keywords are being searched in multiple fields and tables. For example, using keywords:

• uludag
• university

These keywords are being searched in the education, address, contactname, and contactsurname fields in my Members table.

I have to do it so because there must be only one input field for user.

Everything is fine until here, what I want to do is to show the user that this keyword was found on which field? I want to show a field named "Hits".

I'm using SQL Server 2008 and Full-text searching.

You can see an example of what I want to do from xing advanced search section.

Answer 1

You'll know in which field it is found because you know in which field you searched. If you don't care about which field contains the value, then you use the multi-column syntax:

SELECT ...
FROM Members
WHERE CONTAINS((education, address, contactname, contactsurname), 'uludag');

But if you want to search in a specific field then you have to specify only the field you're interested:

SELECT...
FROM Members
WHERE CONTAINS(education, 'uludag');

You can combine multiple fields and preserve the field of origin by unioning multiple queries:

SELECT 'education' as [field origin],... 
FROM Members
WHERE CONTAINS(education, 'uludag')
UNION ALL
SELECT 'address', ...
FROM Members
WHERE CONTAINS(address, 'uludag')
...
UNION ALL
SELECT 'contactsurname', ...
FROM Members
WHERE CONTAINS(contactsurname, 'uludag');

And finally you can use the first form (search in all fields at once) and then check in the client which field contains the search term(s).