Geek asks and answers

Using multiple values ​​in mySQL regexp

I have a request:

SELECT * from arrc_Voucher WHERE VoucherNbr REGEXP "^1002"

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

Now I need to expand this, and I'm not very good at regular expressions. I need to find all the entries where the first four digits are: 1002, 1010, 2015 or 3156. If these were full numbers, I would just use IN:

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

But since I need codes that start with these numbers, this will not work. Can someone help me extend the regex so that it can search for multiple values? To make it more interesting, these values ​​cannot be hardcoded; they are saved in the database configuration table and returned as a comma-delimited string using the php special function. So I need to write a query so that regexp can take the value of the returned string instead of the specific four numbers that I mentioned.

+3
source share
2 answers

You can simply use the following regular expression using the union operator (|).

REGEXP "^(1002|1010|2015|3156)"
+5
source 
VoucherNbr REGEXP "^(1002|1010|2015|3156)"

Not readable, but less backward:

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

- :

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

: .

+5

More articles:


All Articles