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.
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.