Functions for splitting strings

**Examples**

**Usage example**

```
SELECT splitByChar(',', '1,2,3,abcde');
```

```
┌─splitByChar(⋯2,3,abcde')─┐
│ ['1','2','3','abcde']    │
└──────────────────────────┘
```

## splitByNonAlpha[​](#splitByNonAlpha "Direct link to splitByNonAlpha")

Introduced in: v21.9.0

Splits a string separated by whitespace and punctuation characters into an array of substrings.

Note

Setting [`splitby_max_substrings_includes_remaining_string`](/docs/operations/settings/settings#splitby_max_substrings_includes_remaining_string) (default: `0`) controls if the remaining string is included in the last element of the result array when argument `max_substrings > 0`.

**Syntax**

```
splitByNonAlpha(s[, max_substrings])
```

**Arguments**

- `s` — The string to split. [`String`](/docs/sql-reference/data-types/string)
- `max_substrings` — Optional. When `max_substrings > 0`, the returned substrings will be no more than `max_substrings`, otherwise the function will return as many substrings as possible. Default value: `0`. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of selected substrings of `s`. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT splitByNonAlpha('[email protected]');
```

```
['user','domain','com']
```

## splitByRegexp[​](#splitByRegexp "Direct link to splitByRegexp")

Introduced in: v21.6.0

Splits a string which is separated by the provided regular expression into an array of substrings.
If the provided regular expression is empty, it will split the string into an array of single characters.
If no match is found for the regular expression, the string won't be split.

Empty substrings may be selected when:

- a non-empty regular expression match occurs at the beginning or end of the string
- there are multiple consecutive non-empty regular expression matches
- the original string string is empty while the regular expression is not empty.

Note

Setting [`splitby_max_substrings_includes_remaining_string`](/docs/operations/settings/settings#splitby_max_substrings_includes_remaining_string) (default: `0`) controls if the remaining string is included in the last element of the result array when argument `max_substrings > 0`.

**Syntax**

```
splitByRegexp(regexp, s[, max_substrings])
```

**Arguments**

- `regexp` — Regular expression. Constant. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `s` — The string to split. [`String`](/docs/sql-reference/data-types/string)
- `max_substrings` — Optional. When `max_substrings > 0`, the returned substrings will be no more than `max_substrings`, otherwise the function will return as many substrings as possible. Default value: `0`. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of the selected substrings of `s`. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT splitByRegexp('\\d+', 'a12bc23de345f');
```

```
┌─splitByRegex⋯c23de345f')─┐
│ ['a12bc23de345f']        │
└──────────────────────────┘
```

**Empty regexp**

```
SELECT splitByRegexp('', 'abcde');
```

```
┌─splitByRegexp('', 'abcde')─┐
│ ['a','b','c','d','e']      │
└────────────────────────────┘
```

## splitByString[​](#splitByString "Direct link to splitByString")

Introduced in: v1.1.0

Splits a string with a constant `separator` consisting of multiple characters into an array of substrings.
If the string `separator` is empty, it will split the string `s` into an array of single characters.

Empty substrings may be selected when:

- A non-empty separator occurs at the beginning or end of the string
- There are multiple consecutive non-empty separators
- The original string `s` is empty while the separator is not empty

Note

Setting [`splitby_max_substrings_includes_remaining_string`](/docs/operations/settings/settings#splitby_max_substrings_includes_remaining_string) (default: `0`) controls if the remaining string is included in the last element of the result array when argument `max_substrings > 0`.

**Syntax**

```
splitByString(separator, s[, max_substrings])
```

**Arguments**

- `separator` — The separator. [`String`](/docs/sql-reference/data-types/string)
- `s` — The string to split. [`String`](/docs/sql-reference/data-types/string)
- `max_substrings` — Optional. When `max_substrings > 0`, the returned substrings will be no more than `max_substrings`, otherwise the function will return as many substrings as possible. Default value: `0`. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of selected substrings of `s` [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT splitByString(', ', '1, 2 3, 4,5, abcde');
```

```
┌─splitByStrin⋯4,5, abcde')─┐
│ ['1','2 3','4,5','abcde'] │
└───────────────────────────┘
```

**Empty separator**

```
SELECT splitByString('', 'abcde');
```

```
┌─splitByString('', 'abcde')─┐
│ ['a','b','c','d','e']      │
└────────────────────────────┘
```

## splitByWhitespace[​](#splitByWhitespace "Direct link to splitByWhitespace")

Introduced in: v21.9.0

Splits a string which is separated by whitespace characters into an array of substrings.

Note

Setting [`splitby_max_substrings_includes_remaining_string`](/docs/operations/settings/settings#splitby_max_substrings_includes_remaining_string) (default: `0`) controls if the remaining string is included in the last element of the result array when argument `max_substrings > 0`.

**Syntax**

```
splitByWhitespace(s[, max_substrings])
```

**Arguments**

- `s` — The string to split. [`String`](/docs/sql-reference/data-types/string)
- `max_substrings` — Optional. When `max_substrings > 0`, the returned substrings will be no more than `max_substrings`, otherwise the function will return as many substrings as possible. Default value: `0`. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of the selected substrings of `s`. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT splitByWhitespace('  1!  a,  b.  ');
```

```
['1!','a,','b.']
```

## tokens[​](#tokens "Direct link to tokens")

Introduced in: v21.11.0

Splits a string into tokens using the given tokenizer.

Available tokenizers: