Using substr SUBSTR MYSQL-CodePudding

I have an string:

Result":{"CreateDate":"2021-05-27T19:58:33","OrderKey":"6ea1eb40-bd80-4e8b-8e78-a7 ","OrderReference":"app_iheillyy_9816617_AF_e2002"}}

and then I need to extract the position after "OrderKey" with some positions

and than will be this: OrderKey":"6ea1eb40-bd80-4e8b-8e78-a7 ",

its possible with two or more functions ?

CodePudding user response：

Your string is nearly a JSON value provided a little string {" is prepended to it. Then, you'll be able to use JSON_EXTRACT() function in order to get the expected key-value pair such as

SELECT CONCAT('"OrderKey":',
              JSON_EXTRACT(CONCAT('{"',str),"$.Result.OrderKey")
              ) AS key_value
  FROM t;

key_value
----------------------------------------
"OrderKey":"6ea1eb40-bd80-4e8b-8e78-a7 "

Demo

CodePudding user response：

you can use SUBSTRING_INDEX and CONCAT like this:

SET @mystr := 'Result":{"CreateDate":"2021-05-27T19:58:33","OrderKey":"6ea1eb40-bd80-4e8b-8e78-a7 ","OrderReference":"app_iheillyy_9816617_AF_e2002"}}"';

SELECT
CONCAT('"OrderKey',
SUBSTRING_INDEX(
SUBSTRING_INDEX(@mystr, "OrderKey", -1), ",", 1)) ;

sample

MariaDB [bernd]> SET @mystr := 'Result":{"CreateDate":"2021-05-27T19:58:33","OrderKey":"6ea1eb40-bd80-4e8b-8e78-a7 ","OrderReference":"app_iheillyy_9816617_AF_e2002"}}"';
Query OK, 0 rows affected (0.00 sec)

MariaDB [bernd]> 
MariaDB [bernd]> SELECT
    -> CONCAT('"OrderKey',
    -> SUBSTRING_INDEX(
    -> SUBSTRING_INDEX(@mystr, "OrderKey", -1), ",", 1)) ;
 ---------------------------------------------------------------------------------------- 
| CONCAT('"OrderKey',
SUBSTRING_INDEX(
SUBSTRING_INDEX(@mystr, "OrderKey", -1), ",", 1)) |
 ---------------------------------------------------------------------------------------- 
| "OrderKey":"6ea1eb40-bd80-4e8b-8e78-a7 "                                               |
 ---------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)

MariaDB [bernd]>