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

Answer 1

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