Functions for splitting strings

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/splitting-merging-functions.md)

Note

The documentation below is generated from the `system.functions` system table.

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

Introduced in: v1.1.0

Selects substrings of consecutive bytes from the ranges `a-z` and `A-Z` and returns an array of the selected substrings.

**Syntax**

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

**Aliases**: `splitByAlpha`

**Arguments**

- `s` — The string to split. [`String`](/docs/sql-reference/data-types/string)
- `max_substrings` — Optional. When `max_substrings > 0`, the number of returned substrings will be no more than `max_substrings`, otherwise the function will return as many substrings as possible. [`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 alphaTokens('abca1abc');
```

```
┌─alphaTokens('abca1abc')─┐
│ ['abca','abc']          │
└─────────────────────────┘
```

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

Introduced in: v1.1.0

Concatenates string representations of values listed in the array with the provided separator, which is an optional parameter set to an empty string by default.

**Syntax**

```
arrayStringConcat(arr[, separator])
```

**Arguments**

- `arr` — The array to concatenate. [`Array(T)`](/docs/sql-reference/data-types/array)
- `separator` — Optional. Separator string. By default an empty string. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the concatenated string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT arrayStringConcat(['12/05/2021', '12:50:00'], ' ') AS DateString;
```

```
┌─DateString──────────┐
│ 12/05/2021 12:50:00 │
└─────────────────────┘
```

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

Introduced in: v20.5.0

Matches all groups of a string using a regular expression and returns an array of arrays, where each array includes matching fragments from every group, grouped in order of appearance in the input string.

**Syntax**

```
extractAllGroupsVertical(s, regexp)
```

**Aliases**: `extractAllGroups`

**Arguments**

- `s` — Input string to extract from. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `regexp` — Regular expression to match by. [`const String`](/docs/sql-reference/data-types/string) or [`const FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns an array of arrays, where each inner array contains the captured groups from one match. Each match produces an array with elements corresponding to the capturing groups in the regular expression (group 1, group 2, etc.). If no matches are found, returns an empty array. [`Array(Array(String))`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
WITH '< Server: nginx
< Date: Tue, 22 Jan 2019 00:26:14 GMT
< Content-Type: text/html; charset=UTF-8
< Connection: keep-alive
' AS s
SELECT extractAllGroupsVertical(s, '< ([\\w\\-]+): ([^\\r\\n]+)');
```

```
[['Server','nginx'],['Date','Tue, 22 Jan 2019 00:26:14 GMT'],['Content-Type','text/html; charset=UTF-8'],['Connection','keep-alive']]
```

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

Introduced in: v21.11.0

Splits a UTF-8 string into n-grams of length `N`.

**Syntax**

```
ngrams(s, N)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `N` — The n-gram length. [`const UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array with n-grams. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT ngrams('ClickHouse', 3);
```

```
['Cli','lic','ick','ckH','kHo','Hou','ous','use']
```

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

Introduced in: v26.2.0

Reverses the order of substrings in a string separated by a specified separator.
This function splits the string by the separator, reverses the order of the resulting parts,
and joins them back using the same separator. It is useful for parsing domain names,
file paths, or other hierarchical data where you need to reverse the order of components.

Examples:

- reverseBySeparator('[www.google.com](https://www.google.com)') returns 'com.google.www'
- reverseBySeparator('a/b/c', '/') returns 'c/b/a'
- reverseBySeparator('x::y::z', '::') returns 'z::y::x'

**Syntax**

```
reverseBySeparator(string[, separator])
```

**Arguments**

- `string` — The input string to reverse the order of its parts. [`String`](/docs/sql-reference/data-types/string)
- `separator` — The separator string used to identify parts. If not provided, uses '.' (dot). Default: '.' [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a string with substrings ordered from right to left of the original string, joined by the same separator. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic domain reversal**

```
SELECT reverseBySeparator('www.google.com')
```

```
'com.google.www'
```

**Path reversal**

```
SELECT reverseBySeparator('a/b/c', '/')
```

```
'c/b/a'
```

**Custom separator**

```
SELECT reverseBySeparator('x::y::z', '::')
```

```
'z::y::x'
```

**Edge case with dots**

```
SELECT reverseBySeparator('.a.b.', '.')
```

```
'.b.a.'
```

**Single element**

```
SELECT reverseBySeparator('single')
```

```
'single'
```

**Empty separator**

```
SELECT reverseBySeparator('abcde', '')
```

```
'edcba'
```

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

Introduced in: v1.1.0

Splits a string separated by a specified constant string `separator` of exactly one character into an array of substrings.
Empty substrings may be selected if the separator occurs at the beginning or end of the string, or if there are multiple consecutive separators.

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`.

Empty substrings may be selected when:

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

**Syntax**

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

**Arguments**

- `separator` — The separator must be a single-byte character. [`String`](/docs/sql-reference/data-types/string)
- `s` — The string to split. [`String`](/docs/sql-reference/data-types/string)
- `max_substrings` — Optional. If `max_substrings > 0`, the returned array will contain at most `max_substrings` substrings, otherwise the function will return as many substrings as possible. The default value is `0`. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

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

**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**