Skip to main content
Skip to main content
Edit this page

Functions for splitting strings

splitByChar​

Splits a string into substrings separated by a specified character. Uses a constant string separator which consists of exactly one character. Returns an array of selected 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.

Syntax

splitByChar(separator, s[, max_substrings]))

Arguments

  • separator — The separator must be a single-byte character. String.
  • s — The string to split. String.
  • max_substrings — An optional Int64 defaulting to 0. If max_substrings > 0, the returned array will contain at most max_substrings substrings, otherwise the function will return as many substrings as possible.

Returned value(s)

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

The behavior of parameter max_substrings changed starting with ClickHouse v22.11. In versions older than that, max_substrings > 0 meant that max_substring-many splits were performed and that the remainder of the string was returned as the final element of the list. For example,

  • in v22.10: SELECT splitByChar('=', 'a=b=c=d', 2); returned ['a','b','c=d']
  • in v22.11: SELECT splitByChar('=', 'a=b=c=d', 2); returned ['a','b']

A behavior similar to ClickHouse pre-v22.11 can be achieved by setting splitby_max_substrings_includes_remaining_string SELECT splitByChar('=', 'a=b=c=d', 2) SETTINGS splitby_max_substrings_includes_remaining_string = 1 -- ['a', 'b=c=d']

Example

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

Result:

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

splitByString​

Splits a string into substrings separated by a string. It uses a constant string separator of multiple characters as the separator. If the string separator is empty, it will split the string s into an array of single characters.

Syntax

splitByString(separator, s[, max_substrings]))

Arguments

  • separator — The separator. String.
  • s — The string to split. String.
  • max_substrings — An optional Int64 defaulting to 0. When max_substrings > 0, the returned substrings will be no more than max_substrings, otherwise the function will return as many substrings as possible.

Returned value(s)

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 (default: 0) controls if the remaining string is included in the last element of the result array when argument max_substrings > 0.

Example

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

Result:

┌─splitByString(', ', '1, 2 3, 4,5, abcde')─┐
│ ['1','2 3','4,5','abcde']                 │
└───────────────────────────────────────────┘

SELECT splitByString('', 'abcde');

Result:

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

splitByRegexp​

Splits a string into substrings separated by a regular expression. It uses a regular expression string regexp as the separator. If the regexp is empty, it will split the string s into an array of single characters. If no match is found for this regular expression, the string s won't be split.

Syntax

splitByRegexp(regexp, s[, max_substrings]))

Arguments

  • regexp — Regular expression. Constant. String or FixedString.

  • s — The string to split. String.

  • max_substrings — An optional Int64 defaulting to 0. When max_substrings > 0, the returned substrings will be no more than max_substrings, otherwise the function will return as many substrings as possible. Returned value(s)

  • An array of selected substrings. Array(String). 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 s is empty while the regular expression is not empty.

Note

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

Example

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

Result:

┌─splitByRegexp('\\d+', 'a12bc23de345f')─┐
│ ['a','bc','de','f']                    │
└────────────────────────────────────────┘

SELECT splitByRegexp('', 'abcde');

Result:

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

splitByWhitespace​

Splits a string into substrings separated by whitespace characters. Returns an array of selected substrings.

Syntax

splitByWhitespace(s[, max_substrings]))

Arguments

  • s — The string to split. String.

  • max_substrings — An optional Int64 defaulting to 0. When max_substrings > 0, the returned substrings will be no more than max_substrings, otherwise the function will return as many substrings as possible. Returned value(s)

  • An array of selected substrings. Array(String).

Note

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

Example

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

Result:

┌─splitByWhitespace('  1!  a,  b.  ')─┐
│ ['1!','a,','b.']                    │
└─────────────────────────────────────┘

splitByNonAlpha​

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

Syntax

splitByNonAlpha(s[, max_substrings]))

Arguments

  • s — The string to split. String.

  • max_substrings — An optional Int64 defaulting to 0. When max_substrings > 0, the returned substrings will be no more than max_substrings, otherwise the function will return as many substrings as possible. Returned value(s)

  • An array of selected substrings. Array(String).

Note

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

Example

SELECT splitByNonAlpha('  1!  a,  b.  ');

┌─splitByNonAlpha('  1!  a,  b.  ')─┐
│ ['1','a','b']                     │
└───────────────────────────────────┘

arrayStringConcat​

Concatenates string representations of values listed in the array with the separator. separator is an optional parameter: a constant string, set to an empty string by default. Returns the string.

Syntax

arrayStringConcat(arr\[, separator\])

Example

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

Result:

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

alphaTokens​

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

Syntax

alphaTokens(s[, max_substrings]))

Alias: splitByAlpha

Arguments

  • s — The string to split. String.
  • max_substrings — An optional Int64 defaulting to 0. When max_substrings > 0, the returned substrings will be no more than max_substrings, otherwise the function will return as many substrings as possible.

Returned value(s)

Note

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

Example

SELECT alphaTokens('abca1abc');

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

extractAllGroups​

Extracts all groups from non-overlapping substrings matched by a regular expression.

Syntax

extractAllGroups(text, regexp)

Arguments

Returned values

  • If the function finds at least one matching group, it returns Array(Array(String)) column, clustered by group_id (1 to N, where N is number of capturing groups in regexp). If there is no matching group, it returns an empty array. Array.

Example

SELECT extractAllGroups('abc=123, 8="hkl"', '("[^"]+"|\\w+)=("[^"]+"|\\w+)');

Result:

┌─extractAllGroups('abc=123, 8="hkl"', '("[^"]+"|\\w+)=("[^"]+"|\\w+)')─┐
│ [['abc','123'],['8','"hkl"']]                                         │
└───────────────────────────────────────────────────────────────────────┘

ngrams​

Splits a UTF-8 string into n-grams of ngramsize symbols.

Syntax

ngrams(string, ngramsize)

Arguments

Returned values

Example

SELECT ngrams('ClickHouse', 3);

Result:

┌─ngrams('ClickHouse', 3)───────────────────────────┐
│ ['Cli','lic','ick','ckH','kHo','Hou','ous','use'] │
└───────────────────────────────────────────────────┘

tokens​

Splits a string into tokens using the given tokenizer. The default tokenizer uses non-alphanumeric ASCII characters as separators.

Arguments

  • value — The input string. String or FixedString.
  • tokenizer — The tokenizer to use. Valid arguments are default, ngram, split, and no_op. Optional, if not set explicitly, defaults to default. const String
  • ngrams — Only relevant if argument tokenizer is ngram: An optional parameter which defines the length of the ngrams. If not set explicitly, defaults to 3. UInt8.
  • separators — Only relevant if argument tokenizer is split: An optional parameter which defines the separator strings. If not set explicitly, defaults to [' ']. Array(String).
Note

In case of the split tokenizer: if the tokens do not form a prefix code, you likely want that the matching prefers longer separators first. To do so, pass the separators in order of descending length. For example, with separators = ['%21', '%'] string %21abc would be tokenized as ['abc'], whereas separators = ['%', '%21'] would tokenize to ['21ac'] (which is likely not what you wanted).

Returned value

  • The resulting array of tokens from input string. Array.

Example

Using the default settings:

SELECT tokens('test1,;\\ test2,;\\ test3,;\\   test4') AS tokens;

Result:

┌─tokens────────────────────────────┐
│ ['test1','test2','test3','test4'] │
└───────────────────────────────────┘

Using the ngram tokenizer with ngram length 3:

SELECT tokens('abc def', 'ngram', 3) AS tokens;

Result:

┌─tokens──────────────────────────┐
│ ['abc','bc ','c d',' de','def'] │
└─────────────────────────────────┘

alphaTokens​

Introduced in: v1.1

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])

Arguments

  • s — The string to split. 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

Returned value

Returns an array of selected substrings of s. Array(String)

Examples

Usage example

SELECT alphaTokens('abca1abc');

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

arrayStringConcat​

Introduced in: v1.1

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)
  • separator — Optional. Separator string. By default an empty string. const String

Returned value

Returns the concatenated string. String

Examples

Usage example

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

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

extractAllGroupsHorizontal​

Introduced in: v20.5

Matches all groups of a string using the provided regular expression and returns an array of arrays, where each array contains all captures from the same capturing group, organized by group number.

Syntax

extractAllGroupsHorizontal(s, regexp)

Arguments

Returned value

Returns an array of arrays, where each inner array contains all captures from one capturing group across all matches. The first inner array contains all captures from group 1, the second from group 2, etc. If no matches are found, returns an empty array. Array(Array(String))

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 extractAllGroupsHorizontal(s, '< ([\\w\\-]+): ([^\\r\\n]+)');

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

extractAllGroupsVertical​

Introduced in: v20.5

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)

Arguments

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

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']]

extractGroups​

Introduced in: v20.5

Extracts all groups from non-overlapping substrings matched by a regular expression.

Syntax

extractAllGroups(s, regexp)

Arguments

Returned value

If the function finds at least one matching group, it returns Array(Array(String)) column, clustered by group_id (1 to N, where N is number of capturing groups in regexp). If there is no matching group, it returns an empty array. Array(Array(String))

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 extractAllGroups(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​

Introduced in: v21.11

Splits a UTF-8 string into n-grams of ngramsize symbols.

Syntax

ngrams(s, ngram_size)

Arguments

Returned value

Returns an array with n-grams. Array(String)

Examples

Usage example

SELECT ngrams('ClickHouse', 3);

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

splitByChar​

Introduced in: v1.1

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 (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
  • s — The string to split. 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

Returned value

Returns an array of selected substrings. Array(String)

Examples

Usage example

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

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

splitByNonAlpha​

Introduced in: v21.9

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

Note

Setting 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
  • 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

Returned value

Returns an array of selected substrings of s. Array(String)

Examples

Usage example

SELECT splitByNonAlpha('user@domain.com');

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

splitByRegexp​

Introduced in: v21.6

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 (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 or FixedString
  • s — The string to split. 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

Returned value

Returns an array of the selected substrings of s. Array(String)

Examples

Usage example

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

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

Empty regexp

SELECT splitByRegexp('', 'abcde');

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

splitByString​

Introduced in: v1.1

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 (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
  • s — The string to split. 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

Returned value

Returns an array of selected substrings of s Array(String)

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​

Introduced in: v21.9

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

Note

Setting 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
  • 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

Returned value

Returns an array of the selected substrings of s. Array(String)

Examples

Usage example

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

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

tokens​

Introduced in: v21.11

Splits a string into tokens using the given tokenizer. The default tokenizer uses non-alphanumeric ASCII characters as separators.

In case of the split tokenizer, if the tokens do not form a prefix code, you likely want that the matching prefers longer separators first. To do so, pass the separators in order of descending length. For example, with separators = ['%21', '%'] string %21abc would be tokenized as ['abc'], whereas separators = ['%', '%21'] would tokenize to ['21ac'] (which is likely not what you wanted).

Syntax

tokens(value[, tokenizer[, ngrams[, separators]]])

Arguments

  • value — The input string. String or FixedString
  • tokenizer — The tokenizer to use. Valid arguments are default, ngram, split, and no_op. Optional, if not set explicitly, defaults to default. const String
  • ngrams — Only relevant if argument tokenizer is ngram: An optional parameter which defines the length of the ngrams. If not set explicitly, defaults to 3. const UInt8
  • separators — Only relevant if argument tokenizer is split: An optional parameter which defines the separator strings. If not set explicitly, defaults to [' ']. const Array(String)

Returned value

Returns the resulting array of tokens from input string. Array

Examples

Default tokenizer

SELECT tokens('test1,;\\\\ test2,;\\\\ test3,;\\\\   test4') AS tokens;

['test1','test2','test3','test4']

Ngram tokenizer

SELECT tokens('abc def', 'ngram', 3) AS tokens;

['abc','bc ','c d',' de','def']