MySQL Select regular expression pattern to get value in json string

Question

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?

What version are you using? In the later versions, there are JSON functions to do exactly what you want. — Rick James
– Rick James, Commented Feb 20, 2017 at 23:30
If val had its own column, then you would simply be SELECT val FROM table; There are times to use JSON; I don't think this is one of them. — Rick James
– Rick James, Commented Feb 21, 2017 at 1:36

RomanPerekhrest · Accepted Answer · 2017-02-20 23:06:57Z

2

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

edited Feb 20, 2017 at 23:06

answered Feb 20, 2017 at 21:36

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

spacebiker Over a year ago

well, that's not bad at all, but it's not working with all records. And actually I do not understand why because it should. I.e: this value [{"lbl":"","val":" 944 606\u00a08##"}] , returns [{

RomanPerekhrest Over a year ago

@spacebiker, got it. That's because the first SUBSTRING_INDEX couldn't find '"val":"' at offset -2 in your new example

spacebiker Over a year ago

I see, that's why I was asking for a Regex, we do not know the exact number of rows in the json, I need to find another solution but yet, your solution gave me some ideas..

RomanPerekhrest Over a year ago

I have some other ideas, but need your elaborations: is it always be static phone length 987 654 321 - nine numbers?

spacebiker Over a year ago

your are the man! I was trying something similar but your solution works like a charm..

|

Collectives™ on Stack Overflow

MySQL Select regular expression pattern to get value in json string

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related