1

Sorry if this has already been asked but it's a hard one to search for.

I have a column which consists of a list of words like "apple, grape, orange, banana, pear"

If I wanted to find a row which contained a certain word I would use a query like:

SELECT * FROM tablename WHERE 'apple' IN(list_of_words)

However What I would like to do is find all rows which contain several words. Something like this, which doesn't work:

SELECT * FROM tablename WHERE 'apple' IN(list_of_words) AND 'orange' IN(list_of_words)

using something like: 

WHERE list_of_words LIKE "%grape%" AND list_of_words LIKE "%apple%"

Would also not work because then it would find "grapefruit" and "crabapple"

Is there any easy way of doing this already built into SQL?

Thanks.

WheatBeak
  • 1,036
  • 6
  • 12
  • 1
    _I have a column which consists of a list of IDs_ and this is the real problem. Your database isn't properly [normalized](https://en.wikipedia.org/wiki/Database_normalization). Fix this and you won't have any issues running the kinds of queries you want. Plus there will be a whole lot of other benefits, like integrity. – jpw Jan 30 '16 at 01:44
  • 1
    The easy way to do this is to fix your data so you don't have a single column with a list of multiple entries. If you're finding a need to struggle to retrieve the data, it's typically because of poor database design; that's an indication that you need to do some rework of your table schemas to correct the problem, rather than fighting to work around it. – Ken White Jan 30 '16 at 01:44
  • What errors are you getting? – Malkus Jan 30 '16 at 01:47
  • I don't see how that justifies a downvote if it was one of you who did so. The structure of the data is irrelevant to my question. It could have easily been trying to find a couple words in a list of words instead of IDs and the question would still apply. I was simply asking if there was a way to write that query built into SQL, so if there isn't a "no" would have sufficed. To answer the other question, it's just returning 0 rows. – WheatBeak Jan 30 '16 at 01:51
  • Adjusted the original question to be less 'offensive' – WheatBeak Jan 30 '16 at 01:53
  • would have sufficed eh? So you are exempt from the beating the rest of us take ? – Drew Jan 30 '16 at 01:54
  • The structure of the data is *not* irrelevant to your question. If your data were properly normalized, you wouldn't need to be asking the question. Rather than get snarky when people try to help you, you might be better off listening and learning. (And FYI, you're doing it wrong anyway. You need to be using two `LIKE` clauses with wildcards rather than `IN`. Do I get attitude from you for offering that help too?) – Ken White Jan 30 '16 at 02:03
  • I previously edited my question to explain why a LIKE clause with wildcards wouldn't work. – WheatBeak Jan 30 '16 at 02:05
  • I'm not getting an attitude, the point of SO is to answer specific questions, not suggest methods that are better, the best method isn't what the question was about. I'm sure there are other reasons someone might want to use multiple IN clauses that have nothing to do with data structure. All I wanted to know was if it was possible, so yes, it was irrelevant. It'd just be nice if a question could go 30 seconds on here without being downvoted. – WheatBeak Jan 30 '16 at 02:10
  • I do appreciate the point in the right direction, I was just irked by the downvote because I feel like it wasn't a stupid question and actually did have a valid answer, even if it wasn't best practice. Everybody was a newbie at one point and downvoting just basically says "That was a stupid question" and just makes us not want to use the community as the valuable learning tool that it is. Not to mention that with enough downvotes SO just won't let you ask questions at all anymore. – WheatBeak Jan 30 '16 at 15:04

1 Answers1

3

You can use FIND_IN_SET:

SELECT * FROM tbl_name WHERE FIND_IN_SET("orange", list_of_words) OR FIND_IN_SET("apple", list_of_words);

NOTE: list_of_words should be comma separated without spaces after each comma for this to work

PrinceG
  • 982
  • 6
  • 17
  • Thanks, I thought I had tried FIND_IN_SET without success.... Must have done something wrong. – WheatBeak Jan 30 '16 at 02:15
  • And this perpetuates a horrible design and is unhelpful. The assumption is that indexes in RDBMS' should be used, not forced table scans – Drew Jan 30 '16 at 02:17
  • It's helpful because it answers the actual question, which in no way had a request for design critique. – WheatBeak Jan 30 '16 at 02:18
  • 2
    It is helpful for those that follow not to use patches to facilitate poor data designs that everyone is trying to help you with. Point 4 million row data at competing queries and designs someday, and you will understand the wisdom of others. – Drew Jan 30 '16 at 02:19
  • I understand, and in that respect I feel like it wasn't a stupid question because it not only produced the actual answer to the question but also, while not requested, suggestions for a better method. The only issue I have is it being downvoted. I'm sure at some point someone may have the same question as me and will come here and find the answer as well as the proper way to do it. – WheatBeak Jan 30 '16 at 02:24
  • That is what we hope Wheat, and that is what Ken was trying to help you with. Here is a piece of mine [here](http://stackoverflow.com/a/32620163). It's all about maintainability, sanity, and performance. Good luck, seriously. We are all just trying to help each other. – Drew Jan 30 '16 at 02:28
  • It's just annoying when you're first learning and you ask a lot of questions that the "elites" find unworthy and just downvote you to the point where you aren't even allowed to ask questions anymore and have to make another account to do so. – WheatBeak Jan 30 '16 at 02:33