Use single value from comma separated list as parameter

Question

I have table with structure

id| list (VARCHAR)
--|-------
1 | a, b, c
2 | b, c, d
3 | a

I want to use single value from comma separated list as filter in other query. I thought I would achieve this with query like

SELECT * FROM A WHERE column1 IN (SELECT list FROM B WHERE id = 1)

but this way I use value

a, b, c

as filter not, for example,

which I need as a filter.

How can I achieve this?

Fix your database structure so you are not storing lists in comma-delimited strings. SQL has a great feature for storing lists. It is called a table. — Gordon Linoff, Jul 23 '14 at 13:47
what data type is "List"... Set? If so you may need to use FIND_IN_SET http://dev.mysql.com/doc/refman/5.0/en/set.html or `Like` operator not the best performance, but if you have to live with what you have... — xQbert, Jul 23 '14 at 13:50
How do you determine then what "single" value should be returned from the string list of comma separated values? Why a and not c or b? regardless I think your best best if you can't change the db structure is to write a user defined function that will return the first value in the series unless the user specifies a position in the series to return, in which case the UDF would return the value of that position. you can't cast to or from the set data type which may have helped, so a UDF Seems best if structure can't change; as you'll need it in the long run. — xQbert, Jul 23 '14 at 14:05
possible duplicate of http://stackoverflow.com/questions/6152137/mysql-string-split, which is a duplicate of http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — John Ruddell, Jul 23 '14 at 14:38

score 2 · Accepted Answer · answered Jul 23 '14 at 19:56

2

One approach is to use find_in_set() or like. Here is an example:

SELECT *
FROM A a JOIN
     B b
     ON b.id = 1 and
        find_in_set(a.column1, replace(list, ', ', ',')) > 0;

answered Jul 23 '14 at 19:56

Gordon Linoff

1,242,037
58
646
786

John Ruddell · Answer 2 · 2014-07-23T14:11:20.680

1

if you want the first value in your list like you requested then you can just do it like this.

SELECT * 
FROM A 
WHERE column1 IN 
(
    SELECT 
        SUBSTRING_INDEX(`list`, ',', 1) 
    FROM B 
    WHERE id = 1
)

you can get any part of the string by changing the number in the SUBSTRING_INDEX()..

SUBSTRING_INDEX(`list`, ',', 1) -- find first comma and return first part
SUBSTRING_INDEX(`list`, ',', -1) -- find last comma and return last part
SUBSTRING_INDEX(`list`, ',', -2) -- find second to last comma and return last part
SUBSTRING_INDEX(`list`, ',', 2) -- find second to first comma and return first part
.... etc

edited Jul 23 '14 at 14:11

answered Jul 23 '14 at 14:03

John Ruddell

25,283
6
57
86

Thx, but I don't want first value, maybe I made lousy explanation. I want any of comma separated values from that row in list column. – Miki Jul 23 '14 at 14:10
@Miki ok well look here for a function to use.. ive used it before and its helpful http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ – John Ruddell Jul 23 '14 at 14:12
@Miki can you post what result you want the list to look like? – John Ruddell Jul 23 '14 at 14:14
Sounds like he wants to pivot based on comma and return the result as a multiple rows. – xQbert Jul 23 '14 at 17:18
@xQbert its a duplicate. see my comment on the question. – John Ruddell Jul 23 '14 at 18:08

Use single value from comma separated list as parameter

2 Answers2