Find Items in a comma separated database column

Question

I am working on searching algorithm for an ecommerce platform. The platform is able to create variations for different products and all the available variations for a products are included in a comma separated column. Here are all the fields.

product_id - eg 1
variation_combo - eg 54,35,49 (these are comma separated ids for different variations)
product_name - eg "Nike branded Tshirt"

Here is what i would like to achieve, something like

SELECT product_id FROM table where variation_combo contains 35 AND 54 AND 49
SELECT product_id FROM table where variation_combo contains 35 AND 54
SELECT product_id FROM table where variation_combo contains 49

Never, ever store data as comma separated values. It will only cause you lots of trouble. — jarlh, May 17 '17 at 08:47
Having such a `variation_combo` is rather a design flaw. Consider storing the list values in a detail table. — Dmitry Egorov, May 17 '17 at 08:48
yeah, i know this. But the software that i am working on has this flaw and changing it now would be really hard. So we have decided to move on with it. — Omer Farooq, May 17 '17 at 08:50

score 2 · Answer 1 · answered May 17 '17 at 08:45

2

You can use the LIKE keyword

SELECT product_id FROM table where variation_combo LIKE '%35%' AND LIKE '%54%' AND LIKE '%49%'

I hope this helps

answered May 17 '17 at 08:45

Sulaiman Adewale

112
4

What if `variation_combo` has a value of `354`? I doubt it should be selected. – Dmitry Egorov May 17 '17 at 08:47
well i thought of it, but the issue is that if variation_combo contains 154, 135, 145 then this product would be selected even though it shouldn't have been selected. – Omer Farooq May 17 '17 at 08:48
@DmitryEgorov, yup same thought – Omer Farooq May 17 '17 at 08:48
When inserting that combos, it would make more sense if you can wrap each combo with a square bracket, like [32], [40] , so it would be easier when searching – Sulaiman Adewale May 17 '17 at 08:49
@AlienWhale: and then make extra effort to remove the brackets when representing the actual value? Hardly a good way to go. I'd recommend using `RLIKE`. – Dmitry Egorov May 17 '17 at 08:53
No extra efforts, in the look of things, I think the purpose of that column is to search and suggest, which means he already has the IDs with him, he won't need to extract again – Sulaiman Adewale May 17 '17 at 08:58

score 2 · Accepted Answer · edited May 23 '17 at 10:31

2

Refer this: MySQL query finding values in a comma separated string

select product_id from table where find_in_set('35',variation_combo) <> 0   and 
find_in_set('54',variation_combo) <> 0 and find_in_set('49',variation_combo) <> 0

edited May 23 '17 at 10:31

Community

1
1

answered May 17 '17 at 09:13

Kapil

987
5
11

This even provides potential for optimization. Quoting the [docs](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set): _"If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic."_ +1 – Dmitry Egorov May 17 '17 at 09:30
Thanks @DmitryEgorov, i really liked your solution but i tested the performance and kapil's query is a bit faster :) – Omer Farooq May 17 '17 at 10:44
@omerFarooq: Yes, agree, `find_in_set` definitely suits your case best. Leaving my answer just for the record:) – Dmitry Egorov May 17 '17 at 10:49

Dmitry Egorov · Answer 3 · 2017-05-17T09:11:49.507

I strongly recommend to re-design and move the list values into a detail table.

But if you're absolutely bound to the list in a field design, you may use RLIKE to check for a value in the list. If the IDs are just alphanumeric (or more precisely don't contain word boundaries such as 001-01), any single ID may be matched with the \bID\b regex. So your statements would turn to

SELECT product_id
    FROM table 
    where variation_combo RLIKE '\\b35\\b'
        AND variation_combo RLIKE '\\b54\\b'
        AND variation_combo RLIKE '\\b49\\b'

SELECT product_id
    FROM table 
    where variation_combo RLIKE '\\b35\\b'
        AND variation_combo RLIKE '\\b54\\b'

SELECT product_id
    FROM table 
    where variation_combo RLIKE '\\b49\\b'

Find Items in a comma separated database column

3 Answers3