STRING_SPLIT
Description
STRING_SPLIT
splits the string into sub-strings based on pattern and returns the split-index-th split.
STRING_SPLIT uses zero-based indexing, i.e., the first split is at split-index=0. If the requested split does not exist in the string, NULL is returned.
Supported input column types: STRING
Output column type: STRING
Syntax
STRING_SPLIT ( table.column, pattern, split-index )
pattern is the sequence of characters to split the string on. This can be multi-character but does not support wildcards.
split-index is the zero-based index of the requested sub-string after splitting the string. A negative index value selects splits from the end of the string. For example, -1 refers to the last split in the string, -2 the second last and so on.
The following apply to special values of pattern, split-index and the input-string(from table.column
):
split-index is negative: splits are selected from the end of the input-string with -1 being the last split.
pattern is empty: split-index will behave as if indexing into individual characters of the input-string.
pattern does not exist in the string and split-index is zero: the entire string is returned.
pattern appears at the beginning or end of the input-string: the split contains an empty string at the beginning or end respectively.
pattern is repeated consecutively in the input-string: the split behaves as if there are empty strings in between the repeated consecutive patterns.
pattern is identical to input-string and split-index is either zero or one: An empty string is returned.
NULL handling
If any of the inputs are NULL, the result is NULL as well.
Examples
[1] Return the first split after splitting on ','. | ||||||||
| ||||||||
|
[2] Return the first split after splitting on the multi-character pattern ', '. | ||||||||||||||||||||
| ||||||||||||||||||||
|
[3] Return the second split after splitting on the multi-character pattern ', '. | ||||||||||||||||||||
| ||||||||||||||||||||
|
[4] Extract the second character from the input using an empty pattern string. | ||||||||||||||||
| ||||||||||||||||
|
[5] Return the last split using a negative index (-1). | ||||||||||||
| ||||||||||||
|
[6] Applies a FILTER so that only rows with CaseID not equal to 2 are taken into account. | ||||||||||||||||||
| ||||||||||||||||||
|