SQL Query - Search across multiple fields

Question

Hi everyone,

I'm trying to implement a search, where you can enter more than one searchterm to form an AND-Condition. Also it should search in different fields of the database.

So for instance:
You when you enter "Bill Seattle", you should get a record where NAME matches Bill and CITY matches Seattle. You shoudn't get any rows where just the CITY matches Seattle.

Now, this doesn't sound to complicated, but it really seems to be harder than I thought. I've come up with something like this:

SELECT * FROM ADDRESS WHERE
((NAME LIKE 'Bill%') OR (NAME LIKE 'Seattle%')) 
AND
((CITY LIKE 'Bill%') OR (CITY LIKE 'Seattle%'))

This works well in our previous case, but suppose we add another field:

SELECT * FROM ADDRESS WHERE
((NAME LIKE 'Bill%') OR (NAME LIKE 'Seattle%')) 
AND
((CITY LIKE 'Bill%') OR (CITY LIKE 'Seattle%'))
AND
((COMPANY LIKE 'Bill%') OR (COMPANY LIKE 'Seattle%'))

Now it won't return the desired result. What I essentialy need (I guess) is a way to determine if a sub-condition like

 ((COMPANY LIKE 'Bill%') OR (COMPANY LIKE 'Seattle%'))

returns something and if not, i need to ignore that condition.

Btw, I'm using MSSQL '05.

Answer 1

Full Text Indexing allows you to use both Contains and also Conatainstable which is extremely powerful for situations such that you require.

Answer 2

First of all, I don't know how it's done on MSSQL, but you might want to look at fulltext search.

On the other hand, could this be what you really mean:

SELECT * FROM ADDRESS WHERE
((NAME LIKE 'Bill%') OR (CITY LIKE 'Bill%') OR (COMPANY LIKE 'Bill%'))
AND
((NAME LIKE 'Seattle%') OR (CITY LIKE 'Seattle%') OR (COMPANY LIKE 'Seattle%'))

Answer 3

If you want to do this very well, it will be very hard. In an ideal world your program could look at each keyword and determine it's meaning, thus determining which column it should match. But that takes some ingenuity — natural language recognition.

Your example of a search for "Bill Seattle" would require the program to identify "Bill" as a part of a person name, and "Seattle" as a city name. A simple take on that would be to build lists of all known cities and look for keywords there, mark them as city names if found. You could do the same for companies, and then consider all keywords which neither look like cities nor companies to be partial person names. Using this classification building the SQL query should be easy.

But I'm pretty confident that a simple process like I described above will be fragile. So unless you employ some more intelligent parsing solutions (check out the example parser from the OpenCalais project for an idea), I recommend the easy way:

The easy way would be to do full-text search against all the text-fields of your table, as mentioned above, and ordering the results by ranking — that probably is a good 80-20 solution.