Splits the string str into key-value pairs by split1, separates the key-value pairs by split2, and then returns the values of the keys.
Syntax
KEYVALUE_TUPLE(str, split1, split2, key1, key2, ..., keyN)Parameters
str: required. A value of the STRING type. This parameter specifies the string that you want to split.
split1 and split2: required. Values of the STRING type. These parameters specify the strings that are used as delimiters to split the source string. If a key-value pair that is obtained after the source string is split by split1 contains multiple delimiters specified by split2, the returned result is undefined.
key: required. A value of the STRING type. After the source string is split by split1 and split2 in sequence, the value that corresponds to key is returned.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
If the value of split1 or split2 is null, null is returned.
If the value of str or key is null or no key matches, null is returned.
Example
-- Create a table.
create table mf_user (
user_id string,
user_info string
);
-- Insert data into the table.
insert into mf_user values('1','age:18;genda:f;address:abc'),('2','age:20;genda:m;address:bcd');
-- Query the data that you inserted.
SELECT user_id,
KEYVALUE(user_info,';',':','age') as age,
KEYVALUE(user_info,';',':','genda') as genda,
KEYVALUE(user_info,';',':','address') as address
FROM mf_user;
-- It is the same as KEYVALUE.
SELECT user_id,
age,
genda,
address
FROM mf_user LATERAL VIEW KEYVALUE_TUPLE(user_info,';', ':','age','genda','address') ui AS age,genda,address;The following result is returned:
+------------+------------+------------+------------+
| user_id | age | genda | address |
+------------+------------+------------+------------+
| 1 | 18 | f | abc |
| 2 | 20 | m | bcd |
+------------+------------+------------+------------+Related functions
KEYVALUE_TUPLE is a string function. For more information about functions related to string searches and conversion, see String functions.