Search code examples
mysqlregexmysql-workbench

Confusion using regexp in mysql to match a string

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