0

I came across the same issue as the author of this question (PreparedStatement IN clause alternatives?), and wondered if using mysql's REGEXP would be an elegant way of getting the same functionality of IN while using only one PreparedStatement for varying number of values to match? Some example SQL here to show what I am talking about:

SELECT first_name, last_name 
FROM people
WHERE first_name REGEXP ?

Multiple values could be supplied using a string like "Robert|Janice|Michael". I did not see REGEXP mentioned anywhere in that post.

ILikePlants
  • 1

2 Answers2

0

Technically, yes, it is an alternative.

Note, however, that using a regex for matching is less efficient that the in operator ; it incurs more work for the database, that needs to initialize the regex engine, and run it against each and every value (it cannot take advantage of an index).You might not notice it on small volumes, but as your data grows larger this might become an issue. So I would not recommend that as a general solution: instead, just write a few more code lines in your application to properly use the in operator, and use regexes only where they are truly needed.

Aside: if you want to match the entire string, as in does, you need to surround the list of values with ^ and $, so the equivalent for: 

first_name in ('Robert', 'Janice', 'Michael')

Would be:

first name regexp '^(Robert|Janice|Michael)$'
GMB
  • 216,147
  • 25
  • 84
  • 135
0

Another approach:

FIND_IN_SET(name, 'Robert,Janice,Michael')

Yes, that could be substituted in. But it must be a commalist of the desired values. This also works for FIND_IN_SET(foo, '1,123,45'). Note that 12 will not match.

Rick James
  • 135,179
  • 13
  • 127
  • 222