KEYVALUE

Purpose

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

Considerations

• You cannot specify delimiter_1 and delimiter_2 as the same string.

• If the string obtained by splitting with delimiter_1 contains multiple delimiter_2 strings, the first delimiter_2 string 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. The default value is ;. Supported types: VARCHAR and NULL.
  • delimiter_2: The separator for keys and values. The default value is :. Supported types: VARCHAR and NULL.

• key: The key to be searched for (key). Supported types: VARCHAR and NULL. The str string is split using 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