Regexp regular expression for selecting particular string pattern from SELECT query

Question

String will be consider valid if it starts with letters MS and must contain numeric values 0-9, maximum numeric values allowed are 7.

Valid strings :

Invalid sting:

  MS1234567-V2

    MS3434344:old

    YU4534523

    MS4534523768

TY4534523DEL

This the query I tried, I am getting blank result. Where I am going wrong

SELECT MY_STRING_COLUMN
FROM MY_TABLE_NAME
WHERE  `MY_STRING_COLUMN` REGEXP '^[MS]{2}\d{7}';

Reference I got from this Stackoverflow post

Remove the brackets and the quantifier {2}, add anchor - ^MS\d{7}$ (MySQL 8+) / ^MS[0-9]{7}$ (MySQL before 8). [MS] only matches a single char, M or S, thus [MS]{2} matches MM, SS, SM, or MS. Without $, you may match the pattern just at the start of the string, but there may be much more than that later. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 5, 2019 at 13:54
@WiktorStribiżew I'm not sure that \d works with MySQL's REGEXP. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 5, 2019 at 13:55

Tim Biegeleisen · Accepted Answer · 2019-07-05 13:54:38Z

1

Try using [0-9] to represent a digit in your regex pattern:

SELECT MY_STRING_COLUMN
FROM MY_TABLE_NAME
WHERE MY_STRING_COLUMN REGEXP '^MS[0-9]{7}$';

Also note that if you want to match MS at the start of the string, just use ^MS.

answered Jul 5, 2019 at 13:54

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks..let me run this REGEXP!

(Note: MySQL 8.0 and MariaDB do allow \d.)