- Categories:
String & binary functions (General)
SPLIT¶
Splits a given string with a given separator and returns the result in an array of strings.
Contiguous split strings in the source string, or the presence of a split string at the beginningor end of the source string, results in an empty string in the output. An empty separator string resultsin an array containing only the source string. If either parameter is a NULL, a NULL is returned.
You can use functions and constructs that operate onarrays on the result,such asFLATTEN,ARRAY_SIZE, andaccess by index position.
- See also:
Syntax¶
SPLIT(<string>,<separator>)
Arguments¶
stringText to be split into parts.
separatorText to split string by.
Returns¶
The data type of the returned value is ARRAY.
Collation details¶
This function doesn’t support the following collation specifications:
pi(punctuation-insensitive).cs-ai(case-sensitive, accent-insensitive).
The values in the output array don’t include a collation specification and therefore don’t support furthercollation operations.
Examples¶
Split the localhost IP address127.0.0.1 into an array consisting of each of the four parts:
SELECTSPLIT('127.0.0.1','.');
+-------------------------+| SPLIT('127.0.0.1', '.') ||-------------------------|| [ || "127", || "0", || "0", || "1" || ] |+-------------------------+
Access the first element in the returned array by index position:
SELECTSPLIT('127.0.0.1','.')[0];
+----------------------------+| SPLIT('127.0.0.1', '.')[0] ||----------------------------|| "127" |+----------------------------+
Split a string that contains vertical lines as separators, which returns outputthat contains empty strings:
SELECTSPLIT('|a||','|');
+--------------------+| SPLIT('|A||', '|') ||--------------------|| [ || "", || "a", || "", || "" || ] |+--------------------+
Use the result of SPLIT to generate multiple records from a single string using the LATERAL FLATTEN construct.FLATTEN is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view(that is, an inline view that contains correlation referring to other tables that precede it in the FROM clause):
CREATETABLEsplit_test_names(first_nameVARCHAR,childrenVARCHAR);INSERTINTOsplit_test_namesvalues('Mark','Marky,Mike,Maria'),('John','Johnny,Jane');SELECT*FROMsplit_test_names;
+------------+------------------+| FIRST_NAME | CHILDREN ||------------+------------------|| Mark | Marky,Mike,Maria || John | Johnny,Jane |+------------+------------------+
SELECTfirst_name,C.value::STRINGASchildnameFROMsplit_test_names,LATERALFLATTEN(INPUT=>SPLIT(children,','))C;
+------------+-----------+| FIRST_NAME | CHILDNAME ||------------+-----------|| Mark | Marky || Mark | Mike || Mark | Maria || John | Johnny || John | Jane |+------------+-----------+