开发者

Using multiple values in mySQL regexp

开发者 https://www.devze.com 2023-03-03 19:11 出处:网络
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 10

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, 2开发者_JAVA技巧015, 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.


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

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


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消