How to determine if specific value is included in a csv formatted field in MySQL using raw SQL?

Question

specificId field in tbl, value like:

52890,56518,53034,53968,34830,30188,31311,31309,347,40340,31409,38334,37718,40654,26662,39016,26022,50740

These are csv format, how to use raw SQL to figure out, whether this record contains 52890, so if it contains, sql it out, if not contains, I don't want to sql it out in result.

How to do this?

I can use PHP to make this, but it has a pagination problem, I have to make the page size correct, so must be done when I sql it, FYI, the PHP code I used is below:

if(isset($specificId) && is_numeric($specificId)) {
    foreach($list as $k => $item){
        $specificIdArr = explode(',', $item['specificId']);
        if( !in_array($specificId, $specificIdArr) )
            unset($list[$k]);
    }
}

I've tried using find_in_set(), it works, but a little problem, how to remove the result if this field is null by using find_in_set().

Are you guaranteed that all numbers in your "CSV" field have the same length? — PM 77-1, May 14 '14 at 03:06
There are multiple suggested solutions in [MySQL query String contains](http://stackoverflow.com/questions/2602252/mysql-query-string-contains) post. Have a look. — PM 77-1, May 14 '14 at 03:11
I would like to point out that storing multiple values in one column is the incorrect way to use a relational database. — Turophile, May 14 '14 at 06:53

score 1 · Accepted Answer · answered May 14 '14 at 07:02

You can use FIND_IN_SET function to search a specific value in a CSV list.
And use a where clause that checks and omits NULL rows.

Example:

drop table if exists so.tbl_so_q23644829;
create table so.tbl_so_q23644829( csv_column varchar(1024) );
insert into so.tbl_so_q23644829 values( '52890,56518,53034,53968,34830,...' );

select * from so.tbl_so_q23644829 
 where find_in_set( '52890', csv_column ) > 0
   and csv_column is not null;
+-----------------------------------+
| csv_column                        |
+-----------------------------------+
| 52890,56518,53034,53968,34830,... |
+-----------------------------------+

Refer to:

FIND_IN_SET(str,strlist)
- Return the index position of the first argument within the second argument

How to determine if specific value is included in a csv formatted field in MySQL using raw SQL?

1 Answers1