MySQL searching from comma separated string

Question

I have Structure like this table.

          ID                           Category            Publisher

           1                             1,2                  A
           2                             1                    B
           3                             2                    C
           4                             1                    D
           5                             2                    E
           6                             2,3,1                F

I want a query, when i search for category for 1,3 then it's return following

I tried but don't get any optimized way.

          ID                           Category            Publisher

           1                             1,2                  A
           2                             1                    B
           4                             1                    D
           6                             2,3,1                F

Fix your data structure so you use a junction table. SQL has a great data structure for storing lists. It is called a "table" not a "string". — Gordon Linoff, Jan 31 '15 at 16:04
possible duplicate of [Mysql search with comma delimited string](http://stackoverflow.com/questions/6897176/mysql-search-with-comma-delimited-string) — Leandro Papasidero, Jan 31 '15 at 16:49

score 1 · Answer 1 · answered Jan 31 '15 at 16:04

1

select * from your_table
where find_in_set(1, category) > 0
or find_in_set(3, category) > 0

But actually you should never store multiple values in a single column. Better change your table design.

answered Jan 31 '15 at 16:04

juergen d

201,996
37
293
362

Hey, I don't want to pass single time value (1,3). then please do accordingly – Shrikant Gupta Jan 31 '15 at 16:29

score 0 · Answer 2 · edited May 23 '17 at 12:28

While pure SQL does not offer the tools to compare between two sets, this can be implemented in code (JavaScript/PHP) or using stored procedures.

In order to use pure SQL, you could implement a stored procedure to return the comma-separated string as a single-column temporary table (this answer and this post can give you an idea for implementing this part).

And then, utilizing what you have created, assuming the function is implemented STR_SPLIT(<string>, <delimiter>) and returns a table with a single column named colName, you could write:

SELECT DISTINCT `tblName`.*
FROM `tblName`
INNER JOIN STR_SPLIT('1,3',',') `given`
   ON FIND_IN_SET(`given`.`colName`, `category`) > 0

This will take each value and match it separately, which will created duplicates that are going to be eliminated by the DISTINCT statement, making this a non-ideal solution.

I'd recommend using a reference table in this case of data, which will have made this issue a breeze to handle, or implementing a partial solution and finishing code-side.

unable to get you . please provide sql fiddle solution if you can. — Shrikant Gupta, Jan 31 '15 at 17:31
Implement an STR_SPLIT function, then this query will do the trick for you. Then again, this is a complicated solution for a simple issue. If you are still able to introduce schema changes, do it. — Selfish, Jan 31 '15 at 17:39

MySQL searching from comma separated string

2 Answers2