SPLIT_BY_STRING

Description

The SPLIT_BY_STRING function splits an input string into an array of strings based on a specified delimiter string. This function supports multi-character delimiters and may differ from similar functions in other databases in handling empty strings.

Syntax

SPLIT_BY_STRING(<str>, <separator>)

Parameters

Parameter	Description
<str>	The source string to be split. Type: VARCHAR
<separator>	The delimiter string used for splitting. Type: VARCHAR

Return Value

Returns ARRAY type, containing an array of strings split by the delimiter.

Splitting rules:

• Splits at each occurrence of separator in str
• Consecutive separators produce empty string elements
• Separators at the beginning or end of the string produce empty string elements

Special cases:

• If any parameter is NULL, returns NULL
• If str is empty string, returns array with one empty string [""]
• If separator is empty string, str is split by characters (each character becomes an array element)
• If separator doesn't exist in str, returns array containing the original string
• If str contains only separators, returns corresponding number of empty strings based on separator count

Examples

1. Basic string splitting

SELECT SPLIT_BY_STRING('hello', 'l');

+-------------------------------+
| SPLIT_BY_STRING('hello', 'l') |
+-------------------------------+
| ["he", "", "o"]               |
+-------------------------------+

2. Empty separator (split by characters)

SELECT SPLIT_BY_STRING('hello', '');

+------------------------------+
| SPLIT_BY_STRING('hello', '') |
+------------------------------+
| ["h", "e", "l", "l", "o"]    |
+------------------------------+

3. Multi-character separator

SELECT SPLIT_BY_STRING('apple::banana::cherry', '::');

+------------------------------------------------+
| SPLIT_BY_STRING('apple::banana::cherry', '::') |
+------------------------------------------------+
| ["apple", "banana", "cherry"]                  |
+------------------------------------------------+

4. NULL value handling

SELECT SPLIT_BY_STRING(NULL, ','), SPLIT_BY_STRING('hello', NULL);

+-----------------------------+----------------------------------+
| SPLIT_BY_STRING(NULL, ',')  | SPLIT_BY_STRING('hello', NULL)   |
+-----------------------------+----------------------------------+
| NULL                        | NULL                             |
+-----------------------------+----------------------------------+

5. Empty string handling

SELECT SPLIT_BY_STRING('', ','), SPLIT_BY_STRING('hello', 'xyz');

+---------------------------+----------------------------------+
| SPLIT_BY_STRING('', ',')  | SPLIT_BY_STRING('hello', 'xyz')  |
+---------------------------+----------------------------------+
| [""]                      | ["hello"]                       |
+---------------------------+----------------------------------+

6. Consecutive separators

SELECT SPLIT_BY_STRING('a,,b,c', ',');

+-------------------------------+
| SPLIT_BY_STRING('a,,b,c', ',') |
+-------------------------------+
| ["a", "", "b", "c"]           |
+-------------------------------+

7. Separators at beginning and end

SELECT SPLIT_BY_STRING(',a,b,', ',');

+------------------------------+
| SPLIT_BY_STRING(',a,b,', ',') |
+------------------------------+
| ["", "a", "b", ""]           |
+------------------------------+

8. Only contains separators

SELECT SPLIT_BY_STRING('|||', '|');

+----------------------------+
| SPLIT_BY_STRING('|||', '|') |
+----------------------------+
| ["", "", "", ""]           |
+----------------------------+

9. UTF-8 character splitting

SELECT SPLIT_BY_STRING('ṭṛì ḍḍumai ṭṛì', ' ');

+--------------------------------------+
| SPLIT_BY_STRING('ṭṛì ḍḍumai ṭṛì', ' ') |
+--------------------------------------+
| ["ṭṛì", "ḍḍumai", "ṭṛì"]              |
+--------------------------------------+

10. Non-existent separator

SELECT SPLIT_BY_STRING('hello world', 'xyz');

+--------------------------------------+
| SPLIT_BY_STRING('hello world', 'xyz') |
+--------------------------------------+
| ["hello world"]                      |
+--------------------------------------+

Keywords

SPLIT_BY_STRING, SPLIT