select from table using comma separated string

Question

I have a user table in which we store skills of a user that is a comma separated ids like ,These are primary key of skill table

'2,3,4,5'

and i have a search page,In which we have to search using the skills that also multiple entry, the data is like this

'2,3' or '3,4' or '6,7' or '2'

my question is how do i write the where clause any one please help thanks in advance

Nimantha · Accepted Answer · 2022-01-05 04:46:40Z

3

Your database structure should be fixed. You need a junction table, called something like UserSkills, with one row per user and per id.

Here are some reasons:

Storing numbers as strings is a bad idea. You should use the appropriate underlying types.
Your skills are presumably references into another table. Foreign key references need to be of the same type as the thing they are referring to.
SQL has excellent support for tables and mediocre support for strings, so you should use the appropriate data structure.

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Aug 8, 2015 at 11:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Ajmal Ismail · Accepted Answer · 2015-08-08 11:50:29Z

0

All though this is not the right way to store forign keys in the dabase. However, per your problem, you might want to do something like this.

SELECT * FROM tbl_user WHERE skill LIKE '%2, 3%';

answered Aug 8, 2015 at 11:42

Ajmal Ismail

1114 bronze badges