在mysql中使用REGEX对特定字符进行严格的数字检查

Question

In my db data are stored like below:

Q_101_B1
Q_101_B2
Q_101_B17
Q_101_Bch1
Q_101_Ben1
Q_101_B238
Q_101_B

I have to pull only those data which has digits strictly after char 'B', thus in this case it should pull all the data except these two Q_101_Bch1, Q_101_Ben1.

I have tried with this query:

select lang from test_table where lang REGEXP  '^[Q_101_B]|[0-9]'

But unfortunately its pulling all of them.

What should be the exact regex for this query?

Note: Q_101_B can be a different string which will come dynamically, and I will append this while building the query inside my PHP code.

Answer 1

Your original pattern would match any string beginning with the characters Q, _, 1, 0, or B or containing a decimal digit.

Try using this pattern instead:

select lang from test_table where lang REGEXP  '^Q_101_B[0-9]'

This will match any string beginning with Q_101_B followed by a decimal digit.

However, since you indicated you want Q_101_B to be returned as well (with no decimal), you might use this:

select lang from test_table where lang REGEXP  '^Q_101_B([0-9]|$)'

This will match any string beginning with Q_101_B followed by either a decimal digit or the end of the string.

You can see a demonstration of this pattern here.

Also, since you stated you that Q_101_B is just a sample string, and may be replaced by a different string at run-time, I'd recommend you look at using the preg_quote method to safely escape the literal within your pattern.