Using select in MySQL to query a field for a record method that contains a comma-delimited string

Tag:mysql   select    regular    

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) 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 in Pnum field containing 3 or 9 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 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 produces multiple records, such as 33 being found, but MySQL can also use regular, very interesting find_in_set () The position returned by the function and returns 0MYSQL if it does not exist> SELECT find_in_set (' e ', ' h,e,l,l,o '); +------------------------------+|                            Find_in_set (' e ', ' h,e,l,l,o ') |+------------------------------+| 2 |+------------------------------+1 row in Set (0.00 sec) can also be used to sort, as follows;mysql> SELECT * from TEST WHERE ID in (4,2,3); +---- +-------+---------+| ID | PName |  Pnum |+----+-------+---------+| 2 | Product 2 |  2,4,7 | | 3 | Product 3 |  3,4 | | 4 | Product 4 | 1,7,8,9 |+----+-------+---------+3 rows in Set (0.03 sec) if you want to sort by ID 4,2,3? Mysql> SELECT * from TEST WHERE ID in (4,2,3) ORDER by Find_in_set (ID, ' 4,2,3 '); +----+-------+---------+| ID | PName |  Pnum |+----+-------+---------+| 4 | Product 4 |  1,7,8,9 | | 2 | Product 2 |  2,4,7 | | 3 | Product 3 |  3,4 |+----+-------+---------+3 rows in Set (0.03 sec)

Using select in MySQL to query a field for a record method that contains a comma-delimited string