CODEWITHSUNDEEP
mysqljsonregexselect
spacebiker
spacebiker

Reputation: 3875

MySQL Select regular expression pattern to get value in json string

I need some help on a MySQL REGEX_REPLACE pattern. I want to get the first value on a json string:

[{"lbl":"mobile","val":"987 654 321"},
{"lbl":"home","val":"123 456 789"}]

SELECT REGEX_REPLACE(`phones`, PATTERN, '') FROM table;

So, what I would like to get is 987 654 321, What pattern would do the job?

Upvotes: 2

Views: 969

Answers (1)

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

The extended solution using SUBSTRING and LOCATE functions:

SELECT 
    SUBSTRING(chain,
        LOCATE('"val":"', phones) + 7,  -- considering offset as starting position for the final substring
        LOCATE('"}', phones, LOCATE('"val":"', phones)) - (LOCATE('"val":"', phones) + 7)) AS phone
FROM
    table

As '"val":"' occupies 7 chars, the phone number value should start at LOCATE('"val":"', phones) + 7

Upvotes: 2

Related Questions