Vipin Gautam
Vipin Gautam

Reputation: 72

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

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.

Upvotes: 1

Views: 68

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627488

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I think this will do what you want:

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

Here is a db<>fiddle.

Upvotes: 0

Related Questions