[SOLVED-ISH] Another (Probably Simple) SQL Question

[Dom]

Posted this on G+ but I figured I'd ask here as well.

Is there a way to do something like this:

Code:

SELECT fields
FROM table
WHERE column1 LIKE '%some_value%'
OR column1 LIKE '%some_other_value%'
OR column1 LIKE '%some_third_value%';

...but using IN or something similar? It's easy enough to use IN if the values you're looking for don't have % wildcards in them, but with the % wildcards added, using IN like so doesn't match any rows:

Code:

SELECT fields
FROM table
WHERE column1 IN ('%some_value%', '%some_other_value%', '%some_third_value%');

^^^^^ That doesn't work. Using IN is like saying is "select some fields from the table where column1 is equal to 'some_value', or equal to 'some_other_value', or equal to 'some_third_value'".

What I need is "select some fields from the table where column1 is LIKE '%some_value%', or LIKE '%some_other_value%', or LIKE '%some_third_value%'".

Any ideas?

[samhs]

Why?

[Dom]

Well the basic scenario is that the user can enter some search terms into a textbox, separated by commas, like so:

"purple, monkey, dishwasher"

What I need my app to do, is search for each of the terms they enter. I can split the text they enter into it's individual parts, so all I need to do now is have the app search for them.

The field it's looking in contains a title for a forum thread. So in the database there might be a thread called "How to search for a monkey in the forest", for example.

When the user enters "purple, monkey, dishwasher" into the search box, I want my app to search the thread title field for the words "purple", "monkey", and "dishwasher", and match any thread title that has at least one of the words in.

Doing it like this:

Code:

SELECT fields
FROM table
WHERE ThreadTitle IN ('purple', 'monkey', 'dishwasher');

... doesn't work, because it only matches thread titles where the complete title is either "purple", "monkey", or "dishwasher", whereas I want it to match any titles that have those words somewhere in them. So I'd want it to match a thread called "How to search for a monkey in the forest" because it contains the word "monkey".

For that, I need to use LIKE, and I need to use the % wildcard either side of the search term so that it matches anything either side of the word (in this case, it would be "How to search for a" and "in the forest").

[Dom]

PS. Cookies for anyone who spots the Simpsons reference.

[samhs]

So why not just use the OR clause?

[Dom]

For the time being, that's what I've done, but if the user types in 20 different search terms, the SQL statement can get a bit massive. I just figured there'd be a cleaner way to do it instead of having my app loop through each keyword and add OR field LIKE '%value%' to the SQL string.

Not only that, but it's not just the forum titles I want to search through. I'll also need to do the same loop for each keyword, but looking in different fields. I'll be doing the whole thing three times in total; once when it searches the thread titles, once when it searches the thread posts themselves, and once when it searches the "tags" field.

As you can see, the SQL can get a bit hairy if you're looking for 20 different keywords within the thread title, thread posts, and post tags.

[Tony]

waaaay back in the past (and I mean way back) for complex multi query stuff I would use getrows, then stuff it all into an array and get the query to iterate through that - made for a much faster return as it's not doing several billion loops through the DB.

That was in .asp though, not sure if it works in other flavours.

Might not be much help...

[Dom]

Ah right, yeah I could look at doing that. I guess it'll depend on how often these apps I'm building will be used, and how efficient the code is.

[JasonD]

LIKE is never going to be efficient, any slowness or complexity of the code is insignificant.

[Dom]

I wonder how popular forum software like phpBB does it...

[JasonD]

A fulltext index or similar I'd imagine.

[Dom]

Yeah someone on G+ recommended that I look at this http://msdn.microsoft.com/en-US/library/ms345119%28v=SQL.90%29.aspx