Problem in searching multiple keywords

hi, good day.
i have some problem in doing the searching multiple keywords function.

in my layout i have a textbox,user can insert many keywords with the comma separator between each keywords. and then the search function will go to search 3 field in table A, let say field 1 ,field 2 and field 3 see whether got match the keywords or not.
table A
ID |field 1 | field 2 | field 3
----------------------------------------------------------------------------
1 |air conditioner |air conditioner,free| air conditioner
2 | chair | chair,furniture | chair,furniture
3 | hair | hair,human,body | hair,human,body
4 | dog | animal,dog | animal,dog
5 | cat | animal,cat | animal,cat


ok. let say i insert keywords such as --> " AIR,DOG,Animal " in the textbox and search it.
so based on the records from my table. it should query out the records : which ID = 1,4,5(Air Conditioner,Dog and cat)
it should not display the result for Chair OR hair even these two words combine with AIR tis word (Ch +Air) & (H +Air).
the record Cat will show out becoz in the field 2 the "Animal" keywords is matched. therefore it will show out.

so the final result it will display Air conditioner, dog and cat

do u all get wat i mean?
i hope can get some response from u all soon. i need some help on coding.plz help.thanx alot.

P/s : i m using ASP with MS SQL Server 2005

You have several things to do, I guess :o, lo the first thing you should do is build the correct table layout. If I was you, I'll do this:

1- Split keywords in a manner that they only match whole words, and not similar ones, Something I do is to split words or phrases with an special character, for example, "*" or "|". The keywords should look like this:

ID |field 1 | field 2 | field 3
--------------------------------------------------------------------------
1 |*air conditioner*air conditioner*free*air conditioner*

and so on...

This will help you building an easier query to the DB, because you can build it like:

"SELECT * FROM TABLE WHERE field_1 LIKE '*" & keyword1 & "*' OR field_2 LIKE '*" & keyword1 & "*' OR field_3 LIKE '*" & keyword1 & "*' "

As you can see, the search will be based on WHOLE words or phrases, and not similar ones, even using the LIKE statement. Once you've done the query, it'll be easier to do whatever you need to do.
I still don't understand the filed 3, it has the same keywords as field 2 (if you just copy pasted to do the example, then it's OK).

Another thing is that you can do a FOR NEXT statement to search for keywords, remember you have to repeat the search for each field as I did in the query example before. Tell me what you think. Regards, Diego. :cool: