How MySQL queries a comma-delimited string in a field

First we create a comma-delimited string.

CREATE TABLE Test (id int (6) NOT NULL auto_increment,primary KEY (ID), pname varchar (a) not null,pnum varchar (a) NOT null) ;

Then insert the test data with a comma-delimited
INSERT into Test (pname,pnum) VALUES (' Product 1 ', ' 1,2,4 ');
INSERT into Test (pname,pnum) VALUES (' Product 2 ', ' 2,4,7 ');
INSERT into Test (pname,pnum) VALUES (' Product 3 ', ' 3,4 ');
INSERT into Test (pname,pnum) VALUES (' Product 4 ', ' 1,7,8,9 ');

INSERT into Test (pname,pnum) VALUES (' Product 5 ', ' 33,4 ');

Find records with 3 or 9 in the Pnum field
Mysql> SELECT * FROM Test WHERE find_in_set (' 3 ', pnum) OR find_in_set (' 9 ', pnum);
+----+-------+---------+
| ID | PName | Pnum |
+----+-------+---------+
| 3 | Product 3 | 3,4 |
| 4 | Product 4 | 1,7,8,9 |
+----+-------+---------+
2 rows in Set (0.03 sec)

Using the regular
Mysql> SELECT * FROM Test WHERE pnum REGEXP ' (3|9) ';
+----+-------+---------+
| ID | PName | Pnum |
+----+-------+---------+
| 3 | Product 3 | 3,4 |
| 4 | Product 4 | 1,7,8,9 |
| 5 | Product 5 | 33,4 |
+----+-------+---------+
3 Rows in Set (0.02 sec)
This results in multiple records, such as 33 being found.

In a different way

[SQL]View Plaincopy

1. Mysql> SELECT * from test WHERE CONCAT (', ', ' Pnum,', ') REGEXP ' [^0-9]+[3|9][^0-9]+ ';

+----+-------+---------+
| ID | PName | Pnum |
+----+-------+---------+
| 3 | Product 3 | 3,4 |
| 4 | Product 4 | 1,7,8,9 |
+----+-------+---------+
2 rows in Set (0.01 sec)

Problem solving ...

How MySQL queries a comma-delimited string in a field