Confusion using regexp in mysql to match a string

April 27, 2024

I’m new to regexp and mysql but I’m trying to match the following:

SELECT 'abc: bcd' REGEXP '(abc):?\s?b'

This seems to work in a couple of online browser regex simulators but not in mysql.
I couldn’t find any similar questions but I may be missing the right keywords to find my answer
Any help would be greatly appreiciated!

SELECT 'abc: bcd' REGEXP '(abc):?\s?'

This seems to work without the last ‘b’ but I want to specifiy a word following this part to look for

Tested using https://regexr.com/
and https://www.regextester.com/112633
using /…/ig

>Solution :

Your regex is working plese check the changes:

SELECT ('abc: bcd' REGEXP '(abc):?\\s?b') as match_regex;

SELECT ('abc: bcd' REGEXP '(abc):?\\s?') as match_regex;

Better solution:

-- basic matching
SELECT ('abc: bcd' REGEXP '\\w{3}\:\\s*\\w{3}') as match_regex;

-- Regex lookahead & lookbehind 
SELECT ('abc: bcd' REGEXP '(?<=\\w{3})\:\\s*(?=\\w{3})') as match_regex;

Sample code: db<>fiddle