How to get substring from string in MySQL column-CodePudding

Have a nice day to everyone! Source data: There is a conditional Log table In it there is a request column in which such a value is stored:

POST /user/345564523424365 HTTP/1.1
Accept:             application/json, text/plain, */*

Sec-Fetch-Dest:     empty
X-Php-Ob-Level:     1

{"status":"ready"}

It is necessary to get the status value without quotes.

My solution:

SUBSTRING_INDEX(
                    SUBSTRING(Log.request FROM LOCATE("status", Log.request)   9),
                    '"',
                    1
                ) AS status

We find the position of the word status via LOCATE, move the cursor by 9 characters (the number of characters from the beginning of the word status to "ready") and select everything up to the first quotation mark.

Required result: ready

Are there any other options for this solution, maybe there is something simpler? Thanks!

CodePudding user response：

You could match the status keyword and extract the corresponding value with a double SUBSTRING_INDEX function:

SELECT SUBSTRING_INDEX(
           SUBSTRING_INDEX(message, '"status":"', -1), 
       '"', 1) 
FROM messages

Try it here.

CodePudding user response：

If the format of the data is always exactly like your sample; whereas the position of {"status":"ready"} is always at the end, then you may try:

SELECT 
     REGEXP_REPLACE(SUBSTRING_INDEX(val,':',-1),'[^a-zA-Z]','') AS status
...

or perhaps, more specific on the delimiter:

SELECT 
     REGEXP_REPLACE(SUBSTRING_INDEX(val,'{"status":',-1),'[^a-zA-Z]','') AS status
...

Use SUBSTRING_INDEX to extract the value after {"status": then use REGEXP_REPLACE() to replace any non-alphabetical character in the SUBSTRING_INDEX extraction result.

Well, this is largely assuming that you're on MySQL 8 /MariaDB 10.2 versions that supports REGEXP_REPLACE().

Demo fiddle

Additionally, here is an option if the value {"status":"ready"} is not consistently placed at the end of every data row:

SELECT SUBSTRING_INDEX(
         SUBSTRING(val, 
           LOCATE('"status"',val), 
               LOCATE('"}',val)-LOCATE('{"status"',val)),
               '"status":', -1) AS status
...

It's not a simpler solution but it's more convincing when you're not sure if the data is consistent or not.

Demo 2