I am trying to make my regex work in MySQL but it's not working

Question

I have a string stored in my sql database that says

rahul-scored-[ 99 58 21 ]-marks

and I have REGEX for it

^rahul-scored-\[[ \d{2}]* (?:99|58|21) [\d{2} ]*\]-marks$

When I test it in Javascript it return true but if I check it in MySQL using

SELECT * 
from tablexyz 
WHERE columnabc RLIKE '^rahul-scored-\[[ \d{2}]* (?:99|58|21) [\d{2} ]*\]-marks$' 

it does not return any row.

Is there any way to write the exact same regex for MySQL without changing the meaning and sense of REGEX expression.

Answer 1

You can use

REGEXP '^rahul-scored-\\[( (99|58|21))+ \\]-marks$'

See this regex demo.

Details

• ^rahul-scored-\\[ - a literal rahul-scored-[ text at the start of string
• ( (99|58|21))+ - one or more repetitions of a space and then either 99, 58 or 21
• \\]-marks$ - the ]-marks string at the end of string.

Points of interest:

• The regex escapes (like \[ that matches a literal [) must be escaped as \\[ because the pattern is defined using a string literal
• You cannot use non-capturing groups if you use MySQL versions older than 8.

Answer 2

I think this will do what you want:

WHERE columnabc RLIKE '^rahul-scored-\\[( [0-9]{2})* \\]-marks$'

Here is a db<>fiddle.