SQL Query - search across multiple fields

Question

I am trying to do a search in which you can enter several search engines to form an AND-Condition. He should also search in different fields of the database.

So, for example:
you, when you enter Bill Seattle, you should get a record in which NAME corresponds to Bill and CITY corresponds to Seattle. You will not get any rows where only CITY matches Seattle.

Now this does not seem complicated, but it seems to me that it is more difficult than I thought. I came 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%'))

. , ( ), - , -

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

-, , .

Btw, MSSQL '05.

Answer 1

, , MSSQL, .

, , :

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 2

Contains, Conatainstable, , .

Answer 3

, . , , . - .

" " , "" "" . , , , . , , , . SQL- .

But I am sure that a simple process like the one described above will be fragile. Therefore, if you are not using a few more intelligent parsing solutions (look at the sample parser from the OpenCalais project for an idea), I recommend a simple way:

An easy way would be to perform a full-text search on all the text fields of your table, as mentioned above, and arrange the ranking results - this is probably a good 80-20 solution.