CODEWITHSUNDEEP
mysqlsql
Sohil Desai
Sohil Desai

Reputation: 2988

mysql db query for comma separate values

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..

Upvotes: 2

Views: 1439

Answers (3)

rmnaveed
rmnaveed

Reputation: 1

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

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

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.

Upvotes: 2

juergen d
juergen d

Reputation: 204766

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    |
 ...

Upvotes: 2

Related Questions