mysql db query for comma separate values

Question

I have values like something

 | UserName   |    SkillsID      |
 |------------|------------------|
 | Sohil      |    1,2,15,16,19, |
 | Ankur      |    5,8,14,19,    |
 | Lalu       |    4,3,14,15,    |
 | Vinod      |    5,            |
 | Milind     |    8,11          |

now I want to search the Users who have got SkillsID = 5 then result would

 | UserName   |    SkillsID      |
 |------------|------------------|
 | Ankur      |    5,8,14,19,    |
 | Vinod      |    5,            |

How my query would be?

I have thought to split SkillsID into table & then perform query on it but i am not getting any idea to split. can anybody help me???

Thanks in advance to helpers..

Alma Do · Accepted Answer · 2013-09-06 12:00:11Z

2

You can do it with FIND_IN_SET() function or regex:

SELECT * FROM t WHERE SkillsID REGEXP '(^|,)5($|,)'

But you're violating relation DB principle with storing multiple values in one field. You should create a link table for storing that properly and make a foreign key to your user table in it.

answered Sep 6, 2013 at 12:00

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sohil Desai Over a year ago

thank you for help its working. I know i am violating database rules but joins took more time to get records.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Never, never, never store multiple values in one column!

As you see now this will only result in problems. Please normalize your DB structure first like this

 User table  
+-------------------+ 
| userid | username |
+-------------------+
|   1    |  Sohil   |
|   2    |  Ankur   |
+-------------------+

 skill table
 --------------------
 | Userid| SkillID  |
 |-------|----------|
 | 1     |    1     |
 | 1     |    2     |
 | 1     |    15    |
 | 1     |    16    |
 ...
 | 2     |    5     |
 | 2     |    8     |
 | 2     |    14    |
 ...

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 6, 2013 at 11:59

juergen d

205k40 gold badges303 silver badges377 bronze badges

Comments

kleopatra · Accepted Answer · 2013-11-12 11:16:44Z

you can seperate values virtually into different columns using following query and then you can run query using this query as sub query.

select 
substring_index(column_name,',',1) c1,
substr(column_name,length(substring_index(column_name,',',1))+2,length(substring_index(column_name,',',2))-length(substring_index(column_name,',',1))-1) c2,
substr(column_name,length(substring_index(column_name,',',2))+2,length(substring_index(column_name,',',3))-length(substring_index(column_name,',',2))-1) c3,
substr(column_name,length(substring_index(column_name,',',3))+2,length(substring_index(column_name,',',4))-length(substring_index(column_name,',',3))-1) c4,
substr(column_name,length(substring_index(column_name,',',4))+2,length(substring_index(column_name,',',5))-length(substring_index(column_name,',',4))-1) c5,
substr(column_name,length(substring_index(column_name,',',5))+2,length(substring_index(column_name,',',6))-length(substring_index(column_name,',',5))-1) c6
from table_name

Collectives™ on Stack Overflow

mysql db query for comma separate values

3 Answers 3

1 Comment

Never, never, never store multiple values in one column!

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Never, never, never store multiple values in one column!

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related