Purpose
This function is used to extract the value corresponding to a specific key from a key-value pair string.
Considerations
You cannot specify the same string for
delimiter_1anddelimiter_2.If the string obtained by splitting with
delimiter_1contains multipledelimiter_2, the firstdelimiter_2is used as the delimiter to obtain the corresponding Key and Value. For example, executingSELECT KEYVALUE('name:Li:xiang;age:12', ';', ':', 'name');returns the following result:+----------------------------------------------------+ | KEYVALUE('name:Li:xiang;age:12', ';', ':', 'name') | +----------------------------------------------------+ | Li:xiang | +----------------------------------------------------+ 1 row in set
Syntax statement
KEYVALUE(str, [delimiter_1, delimiter_2,] key)
Parameters
str: A string containing multiple key-value pairs. Supported data types:VARCHAR,TINYTEXT,TEXT,LONGTEXT, andNULL.JSONis not supported.delimiter_1, delimiter_2,: Optional. Specifies the delimiters. Valid values:delimiter_1: The delimiter for key-value pairs. Default value:;. Supported data types:VARCHARandNULL.delimiter_2: The key-value delimiter. Default value::. Valid types:VARCHARandNULL.
key: The key (key) to be searched for. Supported types:VARCHARandNULL. This function splits thestrbydelimiter_1anddelimiter_2, and returns thevaluecorresponding to thekeyvalue.
Return type
- If any parameter in the expression is
NULL, the return value isNULL. - If the corresponding key is not found, the function returns
NULL. - If multiple key-value pairs match, return the
valuecorresponding to the first matchingkey.
Examples
SELECT
KEYVALUE(NULL, ',', '=', 'b') AS key_value_null,
KEYVALUE('a=1,b=2,c=3', NULL, '=', 'b') AS delimiter_1_null,
KEYVALUE('a=1,b=2,c=3', ',', NULL, 'b') AS delimiter_2_null,
KEYVALUE('a=1,b=2,c=3', ',', '=', NULL) AS key_null,
KEYVALUE('a=1,b=2,c=3', ',', '=', 'b') AS b,
KEYVALUE('a=1,b=2,c=3,b=4', ',', '=', 'b') AS bs,
KEYVALUE('a:1;b:2;c:3', 'b') split_default;
The return result is as follows:
+----------------+------------------+------------------+----------+------+------+---------------+
| key_value_null | delimiter_1_null | delimiter_2_null | key_null | b | bs | split_default |
+----------------+------------------+------------------+----------+------+------+---------------+
| NULL | NULL | NULL | NULL | 2 | 2 | 2 |
+----------------+------------------+------------------+----------+------+------+---------------+
1 row in set