mysql query to search comma separated string data from database field

Question

I have comma separated string data in my database field name 'status' for example:("2,5,6,9,16") and i have to search whose status is exactly '6'.

I am doing like this.

   $offers = DB::query("select id,status from table  WHERE (status LIKE '%6%')")->execute()->as_array();
        $countOffer = 0;
        if($offers){
        foreach ($offers as $offer){
            $checks= explode(',',$offer['status']);
            foreach($checks as $check){
              if($check == '6'){
                $countOffer = $countOffer+1;  
              }  
            }
        }
        }

thanks in advance.

http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php — Mihai, Jul 31 '14 at 08:11

ngrashia · Answer 1 · 2014-07-31T08:15:23.120

1

This should help you:

select id,status from table  WHERE (',' || status || ',' LIKE '%,6,%');

Another option would be:

 SELECT  STATUS FROM TABLE WHERE 
 (    STATUS LIKE '%,6,%'  
   OR STATUS LIKE '%,6' 
   OR STATUS LIKE '6,%' 
   OR STATUS='6');

I would recommend the first option personally!

edited Jul 31 '14 at 08:15

answered Jul 31 '14 at 08:09

ngrashia

9,869
5
43
58

score 0 · Answer 2 · answered Jul 31 '14 at 08:13

The usual approach to this kind of query is the use of FIND_IN_SET:

select id, status from table  WHERE FIND_IN_SET('6',status) > 0;

Note:

Storing comma separated lists in columns is almost ever asking for trouble. Please consider normalizing your database.

mysql query to search comma separated string data from database field

2 Answers2