Grouping in REGEXP_SUBSTR() in MYSQL 8.x

Question

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

Its not JSON. Its just formatted text. Can this be done using regexp_substr()? — Buddy Li, Jul 20 '22 at 08:34

score 1 · Accepted Answer · answered Jul 20 '22 at 08:06

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

Grouping in REGEXP_SUBSTR() in MYSQL 8.x

1 Answers1