How to query comma separated string?

Question

Table_a

column name: list_id
record 1: 1,2,5,6,8
record 2: 1,3,2
record 3: 6,7,2
record 4: 9,8,0

Table_b

id ='2';

How to select records that have id='2' in the comma separated string? From the above example it should return record 1,2 and 3.

Query(How to amend this query, please?):

SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id;

@Dagon, thats the problem, I'm modifying existing system which has loads of data which is impossible to normalise now. — 112233, Mar 31 '16 at 07:12
@Keren see [this link](http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) — Yash, Mar 31 '16 at 07:19
@sagi . Yeah, could be. I just wanted to point out the `Like` operator and wildcards. — print x div 0, Mar 31 '16 at 07:24

score 3 · Accepted Answer · answered Mar 31 '16 at 07:14

3

use find_in_set function, but it is not optimized way you should normalize your data.

SELECT * FROM 
Table_a AS a 
JOIN Table_b AS b ON FIND_IN_SET(b.id,a.list_id)

answered Mar 31 '16 at 07:14

Zafar Malik

score 0 · Answer 2 · answered Mar 31 '16 at 07:21

0

This should work for you

SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id and 
concat(',',Table_a.list_id,',') like '%,2,%'

answered Mar 31 '16 at 07:21

ManiMuthuPandi

Why @sagi. Please share the reason so I can edit here – ManiMuthuPandi Mar 31 '16 at 07:28
bcaz the last number of list_id dont have comma – J.K Mar 31 '16 at 07:30
In my query concat(',',Table_a.list_id,',') I have attached comma at both the ends. – ManiMuthuPandi Mar 31 '16 at 07:34
And because the where clause will be false id <> list_id – sagi Mar 31 '16 at 07:42

2 Answers2