KEYVALUE

Purpose

This function is used to extract the value corresponding to a specific key from a key-value pair string.

Limitations and considerations

• You cannot specify the same string for both delimiter_1 and delimiter_2.

• If the string obtained by splitting with delimiter_1 contains multiple delimiter_2 separators, the first occurrence of delimiter_2 is used as the separator to obtain the corresponding key and value. For example, executing SELECT 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

KEYVALUE(str, [delimiter_1, delimiter_2,] key)

Parameters

• str: the string containing multiple key-value pairs. Supported types: VARCHAR, TINYTEXT, TEXT, LONGTEXT, and NULL. JSON is not supported.

• delimiter_1, delimiter_2, (optional): the separators. Details are as follows:

  • delimiter_1: the separator for key-value pairs. Default value: ;. Supported types: VARCHAR and NULL.
  • delimiter_2: the separator for keys and values. Default value: :. Supported types: VARCHAR and NULL.

• key: the key to be searched for (key). Supported types: VARCHAR and NULL. The str string is split by delimiter_1 and delimiter_2, and the value corresponding to the key is returned.

Return type

• If any parameter in the expression is NULL, the result is NULL.
• If the corresponding key is not found, the function returns NULL.
• If multiple key-value pairs match, the value corresponding to the first matching key is returned.

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