Free text search from multiple columns

[d2ned]

Hi guys,

I'm struggling with this (probably very basic) problem with an SQL query that's supposed to let the user choose a phrase/word that is then searched from a single table but all columns. (Or the ones that are VARCHAR, MEMO & such) It's basically a free classifieds system written in ASP, so there really is only one table, but multiple columns.

I tried to go about it like SELECT * FROM table WHERE col_1 OR col_2 OR col_3 LIKE '%" & Replace(Keyword, "'", "''") & "%' and so forth, but the OR returns every record no matter the word being searched for and if I use AND it returns none. I tried using this link

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q286787

as a advice but had no luck using WHERE CONTAINS, all I got was some really interesting server processing errors about the syntax..

I'd really appreciate all help on this, I really haven't used SQL that much before and I'm pretty much lost.. :/

-J

 

[PHV]

Try something like this:
t=" LIKE '%" & Replace(Keyword, "'", "''") & "%'"
sql="SELECT * FROM table WHERE col_1" & t & " OR col_2" & t & " OR col_3" & t

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[d2ned]

Hi,

I tried PHV's approach last month, but I kept getting errors about the syntax. I revisited your advice now and tried to modify it further, but I can't seem to get it working. What could be wrong with this approach? I'm still trying to accomplish the same thing - I'd need a sql statement that does a SELECT * FROM mytable WHERE column1 OR column2 OR column3 LIKE keyword, and get correct results. Any advice is highly appriciated!

-J