IN string MySQL

Question

I have in my database this table :

+----+-----------+
| ID |    JOB    |
+----+-----------+
|  1 | Developer |
|  2 | Designer  |
+----+-----------+

I want to run this query that's normaly will get the ID : 2

select j.ID from jobs j where 'Designer' in ( j.JOBS )

unfortunately it's not working when I have like this table

+----+------------------------------------------+
| ID |                   JOB                    |
+----+------------------------------------------+
|  1 | Developer                                |
|  2 | Designer,administrator,analyst,Developer |
+----+------------------------------------------+

Because the value of j.JOBS is string : ' Designer,administrator,analyst,Developer '

not like multipe values separeted by commat like this : ' Designer ', ' administrator ' , ' analyst ' ,' Developer'

So, how can I solve this problem, and I wish I explain well my question .thanks

First off, *don't* store multiple values in one field. Normalize your data, and make a link table. Second, if you *must* do it this way, use `FIND_IN_SET('Designer', j.JOBS)` (see: http://stackoverflow.com/questions/4155873/find-in-set-vs-in). — gen_Eric, Feb 03 '14 at 22:14
Rocket Hazmaz is right. You should have a job table (with only single job in job column), a people table and a people_job join table. — marekful, Feb 03 '14 at 22:16
@RocketHazmat Thanks. yes I know I have to use links between tables, but in my case I must store multipe values in one fields. — 2dar, Feb 03 '14 at 22:29

score 7 · Accepted Answer · answered Feb 03 '14 at 22:15

Consider your troubles to be punishment for storing a list of values in a single column. You should have a separate table with one row per id and one row per job. No lists in a row. Just one item per row in an association table.

The answer to your question, though, is find_in_set():

select j.ID
from jobs j
where find_in_set('Designer', j.JOBS ) > 0;

score 3 · Answer 2 · answered Feb 03 '14 at 22:15

3

Maybe you should use LIKE statement, and something like WHERE j.JOBS LIKE "%Designer%".

answered Feb 03 '14 at 22:15

spalac24

1,076
1
7
16

IN string MySQL

2 Answers2