mysqlregexp-substr

Grouping in REGEXP_SUBSTR() in MYSQL 8.x

I am trying regexp as I know it but doesn't seem to be working in MYSQL v8.0. I am trying to extract the filename (something.txt) from the some_str variable. It keeps returning NULL. Not sure what I am doing wrong in the pattern.

set @some_str = "{'A': 1234, 'fname': 'something.txt'}";
select regexp_substr(@some_str, "\\{'\w+':\s+\d+,\s+'\w+':\s+'(.+)'\\}") ;
-- should return:  something.txt
Solution 
  • set @some_str = "{'A': 1234, 'fname': 'something.txt'}";
SELECT JSON_UNQUOTE(JSON_EXTRACT(REPLACE(@some_str, '''', '"'), '$.fname'));

     
| JSON_UNQUOTE(JSON_EXTRACT(REPLACE(@some_str, '''', '"'), '$.fname')) |
| :------------------------------------------------------------------- |
| something.txt                                                        |

    db<>fiddle here