mysql: how to select imploded array value

Question

So I have a column that store imploded array values via , , how can I select array value with the following condition:

if column stored values like below:

+-----+------------------------------------------------------------+
| row |                           column                           |
+-----+------------------------------------------------------------+
| 1   | lorem ipsum,lorem ipsum dolor,lorem ipsum dolor sit amet   |
| --  | --                                                         |
| 2   | consectetuer adipiscing,lorem ipsum amet,lorem ipsum dolor |
| --  | --                                                         |
| 3   | lorem ipsum,consectetuer adipiscing,lorem ipsum dolor      |
+-----+------------------------------------------------------------+

I want to select the rows that has lorem ipsum value, I know we can use Like but if use like, it select lorem ipsum dolor or lorem ipsum amet too! I try following query:

select * from table where column like '%lorem ipsum%'

# my expected rows :1,3
# query result :1,2,3

That is going to be a very inefficient query no matter how you write it. — ryantxr, Aug 21 '17 at 18:37
I would say you **might** get away with this **if** you did not want to query based on this column. — ryantxr, Aug 21 '17 at 18:41
Or better yet, [don't store them like that](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — Don't Panic, Aug 21 '17 at 18:44

score 2 · Accepted Answer · answered Aug 21 '17 at 18:35

2

If the value is comma-separated (and not comma+space separated), you can use FIND_IN_SET:

SELECT * FROM `table` WHERE FIND_IN_SET('lorem ipsum', `column`);

answered Aug 21 '17 at 18:35

rickdenhaan

10,857
28
37

In my original query I use join, how to use `FIND_IN_SET` in this case? is `FIND_IN_SET('lorem ipsum',``cast.meta_value``)` true ? – Question Aug 21 '17 at 18:41
Yes, `FIND_IN_SET('value', columnname)`, you can add the table/alias name to the column name if necessary. – rickdenhaan Aug 21 '17 at 18:46

mysql: how to select imploded array value

1 Answers1

Linked