Using multiple values in mySQL regexp

Question

I have a query:

SELECT * from arrc_Voucher WHERE VoucherNbr REGEXP "^1002"

This works well - VoucherNbr is a 16-character code, and this query finds all records where the first four digits are 1002.

Now I need to expand this, and I'm not very good with regular expressions. I need to be able to find all records where the first four digits are 1002, 1010, 2015, or 3156. If those were the complete numbers, I'd just use IN:

SELECT * from arrc_Voucher WHERE VoucherNbr IN (1002, 1010, 2015, 3156)

But since I need codes that start with those numbers, that won't work. Can anyone help me expand the regexp so that it can look for multiple values? To make it more interesting, these values can't be hard-coded; they're stored in a database config table and returned as a comma-delimited string by a custom php function. So I need to be able to write the query so the regexp can take the value of the returned string instead of the specific four numbers I mentioned.

Answer 1

You could just use the following regex, using the union operator (|).

REGEXP "^(1002|1010|2015|3156)"

Answer 2

VoucherNbr REGEXP "^(1002|1010|2015|3156)"

Not as readable, but less backtracking:

VoucherNbr REGEXP "^(10(02|10)|2015|3156)"

You can surely do something like the following too:

LEFT(VoucherNbr, 4) IN (1002, 1010, 2015, 3156)

Update: Added second and third solution.