regexp mysql group

Question

I try get name of city's from string '{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}' I try this regexp:

SELECT REGEXP_SUBSTR('{\"travelzoo_hotel_name\":\"Graduate Minneapolis\",\"travelzoo_hotel_id\":\"223\",\"city\":\"Minneapolis\",\"country\":\"USA\",\"sales_manager\":\"Stephen Conti\"}'
,'(?:.city...)([[:alnum:]]+)');

I have: '"city":"Minneapolis' Me need only name of city:Minneapolis. How to use groups in queries?

My example in regex101 Help me Please

I'ts not possible to reference to a group in mysql [https://stackoverflow.com/questions/7058209/reference-to-groups-in-a-mysql-regex](https://stackoverflow.com/questions/7058209/reference-to-groups-in-a-mysql-regex) — Laurent Mouchart, Jun 01 '18 at 08:40
Possible duplicate of [Reference to groups in a MySQL regex?](https://stackoverflow.com/questions/7058209/reference-to-groups-in-a-mysql-regex) — Laurent Mouchart, Jun 01 '18 at 08:42
@LaurentMouchart It depends which version. [MySQL 8.0 supports more than lookaheads](https://dev.mysql.com/doc/refman/8.0/en/regexp.html). — Wiktor Stribiżew, Jun 01 '18 at 08:48

Wiktor Stribiżew · Accepted Answer · 2018-06-01T20:37:38.753

I assume you are using MySQL 8.x that uses ICU regex expressions.

It looks like the string you want to process is JSON. You may use JSON_EXTRACT with JSON_UNQUOTE and a '$.city' as JSON path then:

JSON_UNQUOTE(JSON_EXTRACT('{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}', '$.city'))

will return Minneapolis.

In your regex, the non-capturing group pattern is still matched and appended to the match value. "Non-capturing" only means no separate memory buffer is alotted to the text captured with a grouping construct. So, you may fix it with '(?<="city":")[^"]+' pattern where (?<="city":") is a positive lookbehind that matches "city":" but does not put it into the match value. The only text you will have in the output is the one matched with [^"]+, 1+ chars other than ".

JSON_EXTRACT returned "Minneapolis", it's not valid. Thx for regular, it's work — Вовчик Николаенко, Jun 01 '18 at 20:33
@ВовчикНиколаенко You can use `JSON_UNQUOTE`. I updated the answer. Note that you should really consider using JSON parser to handle JSON data. — Wiktor Stribiżew, Jun 01 '18 at 20:36

regexp mysql group

1 Answers1