0

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.

Abhishek Madhani
  • 1,155
  • 4
  • 16
  • 26

4 Answers4

1

You can do like this:

SELECT * FROM table AS t WHERE t.list REGEXP '^9,|,9$|,9,' OR t.list =9
Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
1

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.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • I had thought of using `FIND_IN_SET` in this fashion, but it requires additional coding outside MySQL to multiply the function number of times and values appear. – Abhishek Madhani Jan 15 '14 at 07:00
  • And it would make query as heavy weight if for instance i encounter a situation requiring me to match 500 values, isn't `REGEXP` an apt solution ? – Abhishek Madhani Jan 15 '14 at 07:02
  • All the heaviness is caused by the improper design. REGEXP won't make it any lighter for you. REGEXP won't be any better in this case and you still need to split the query string. In both cases you always causing a full scan on your table. If you need to match 500 values than create a temp table insert these values and join with your table using `FIND_IN_SET()`. But again what you should really be doing is to normalizing you schema. – peterm Jan 15 '14 at 07:06
  • I understand that NORMALIZATION is the way to go. But the table I am working with is further used for searching in different module, and normalization is slowing it down, hence we went with idea of denormalizing it and saving values as concatenated with `,` – Abhishek Madhani Jan 15 '14 at 07:18
0

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/

niyou
  • 875
  • 1
  • 11
  • 23
  • Yes I was thinking on same line, however the `|` will match in EITHER OR pattern. Is there way to ensure that all of the values are present – Abhishek Madhani Jan 15 '14 at 06:59
0

try the fallowing sql statement:

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