MySQL: Usage of 'REGEXP' instead of 'F

问题:

I am having limitation in using MySQL's FIND_IN_SET function for searching array in set. Hence thinking of using of REGEXP. However can anyone help me constructing it.

E.g. My requirement

SELECT * FROM table AS t WHERE FIND_IN_SET('1,2,3', t.list);

Hence thinking of using REGEXP function to search array within set.

SELECT * FROM table AS t WHERE t.list REGEXP '1,2,3';

Can anyone help me building this REGEXP.

回答1:

You can do like this:

SELECT * FROM table AS t WHERE t.list REGEXP '^9,|,9$|,9,' OR t.list =9

回答2:

You can split your search string and continue to use FIND_IN_SET()

SELECT * 
  FROM `table` AS t 
 WHERE FIND_IN_SET('1', t.list)
   AND FIND_IN_SET('2', t.list)
   AND FIND_IN_SET('3', t.list)

Better yet normalize your data by introducing a many-to-many table.

回答3:

For your requirements you can easily use:

SELECT * 
FROM table1 AS t 
WHERE t.list REGEXP '1|2|3';

To learn about regular expressions take look at this software: http://www.weitz.de/regex-coach/

回答4:

try the fallowing sql statement:

SELECT * 
FROM table AS t 
WHERE t.list REGEXP '^(1$|2$|3$)';