mysql query: pull all rows where field2 has 01, 02, or 04 as any part of it

I have a query:

SELECT * 
  FROM table 
  WHERE field1 IS NULL 
    AND (field2 LIKE '%01%' OR 
         field2 LIKE '%02%' OR 
         field2 LIKE '%04%'
        )

The goal is to pull all rows where field2 has 01, 02, or 04 as any part of it. field2 typically has comma separated values like 01,02,03 or sometimes just a single 02. However, when I run this query, I get back only one result that has 01,02,03,04 as the value, and it isn’t the only one in the table with that exact same data.

This is essentially what the table looks like:

|id|field1  |field2             
| 1| NULL|01,02,03,04
| 2| NULL|01,02,03,04
| 3| NULL|01,02,04

Answer

You can use REGEXP function:

SELECT *     
  FROM table     
 WHERE field1 IS NULL       
   AND (field2 REGEXP '0[124]') 

e.g:

SELECT *
  FROM  (
                 SELECT 'SKJDFHSKDJF01KJSAKSJFHK DSFKJHSKDFJ' INFO
                 UNION ALL
                 SELECT 'SKJDFHSKDJF02KJSAKSJFHK DSFKJHSKDFJ' INFO
                 UNION ALL
                 SELECT 'SKJDFHSKDJF03KJSAKSJFHK DSFKJHSKDFJ' INFO
                 UNION ALL
                 SELECT 'SKJDFHSKDJF04KJSAKSJFHK DSFKJHSKDFJ' INFO
                 UNION ALL
                 SELECT 'SKJDFHSKDJF0KJSAKSJFHK DSFKJHSKDFJ' INFO
                 UNION ALL
                 SELECT 'SKJDFHSKDJF09KJSAKSJFHK DSFKJHSKDFJ' INFO
                ) b
 WHERE INFO REGEXP '0[124]'