Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Functions for splitting strings

View as MarkdownEdit this pageReport an issueSet up docs MCP

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

Aliases: array_to_string

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 │
└─────────────────────┘

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

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

naiveBayesNgrams

Introduced in: v26.7.0

Splits text into n-grams using the same tokenization as a Naive Bayes dictionary (the NAIVE_BAYES layout): byte, codepoint, or token mode, with optional boundary padding. Use it to build the pre-aggregated (ngram, class_id, count) training data such a dictionary consumes, so the training n-grams match exactly what naiveBayesClassifier produces at query time.

Syntax

naiveBayesNgrams(text, n, mode[, start_token, end_token])

Arguments

  • text — Text to split into n-grams. String
  • n — N-gram size, from 1 to 1024. const UInt
  • mode — Tokenization mode: ‘byte’, ‘codepoint’, or ‘token’. const String
  • start_token — Optional. Boundary token prepended (n-1) times to the input; a number for ‘byte’/‘codepoint’, a literal for ‘token’. Empty means no padding. const String
  • end_token — Optional. Boundary token appended (n-1) times to the input. const String

Returned value

The n-grams of the input text. Array(String)

Examples

Token bigrams

SELECT naiveBayesNgrams('the cat sat', 2, 'token');
['the cat','cat sat']

Token bigrams with boundary padding

SELECT naiveBayesNgrams('cat', 2, 'token', '<s>', '</s>');
['<s> cat','cat </s>']

Byte bigrams with boundary padding (result shown as hex)

SELECT arrayMap(x -> hex(x), naiveBayesNgrams('xy', 2, 'byte', '0x01', '0xFF'));
['0178','7879','79FF']

Code-point bigrams with boundary padding (result shown as hex)

SELECT arrayMap(x -> hex(x), naiveBayesNgrams('ab', 2, 'codepoint', '0x10FFFE', '0x10FFFF'));
['F48FBFBE61','6162','62F48FBFBF']

ngrams

Introduced in: v21.11.0

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

Syntax

ngrams(s, N)

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

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’) 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
  • separator — The separator string used to identify parts. If not provided, uses ‘.’ (dot). Default: ‘.’ String

Returned value

Returns a string with substrings ordered from right to left of the original string, joined by the same separator. 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

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.

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(',', '1,2,3,abcde')─┐
│ ['1','2','3','abcde']           │
└─────────────────────────────────┘

splitByNonAlpha

Introduced in: v21.9.0

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

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

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');
┌─splitByRegexp('\\d+', 'a12bc23de345f')─┐
│ ['a','bc','de','f']                    │
└────────────────────────────────────────┘

Empty regexp

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

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

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');
┌─splitByString(', ', '1, 2 3, 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.0

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

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

Splits a string into tokens using the given tokenizer.

Available tokenizers:

  • splitByNonAlpha splits strings along non-alphanumeric ASCII characters (also see function splitByNonAlpha).
  • splitByString(S) splits strings along certain user-defined separator strings S (also see function splitByString). The separators can be specified using an optional parameter, for example, tokens(value, 'splitByString', [', ', '; ', '\n', '\\']). Note that each string can consist of multiple characters (', ' in the example). The default separator list, if not specified explicitly, is a single whitespace [' '].
  • splitByRegexp(re) splits strings along a user-defined regular expression separator re (also see function splitByRegexp). The regular expression is mandatory, for example, tokens(value, 'splitByRegexp', '[^\p{L}\p{N}#+]+'). Unlike splitByString, a regular expression separator can preserve tokens containing special characters (such as C++ or C#).
  • asciiCJK splits strings into tokens using Unicode word boundary rules (similar to UAX #29). ASCII alphanumeric characters and underscores form tokens with connectors (: for letters, . and ' for same-type characters). Non-ASCII Unicode characters become single-character tokens.
  • chinese segments Chinese text into words using a dictionary and a hidden Markov model (the algorithm follows jieba; the embedded dictionary and model data are derived from cppjieba). Unlike asciiCJK, which treats every non-ASCII character as a single-character token, chinese groups consecutive Chinese characters into words, which yields more meaningful tokens and higher search quality for Chinese text. An optional granularity argument is either coarse_grained (the default) or fine_grained; the latter additionally enumerates overlapping sub-words, improving recall at the cost of a larger index.
  • icu(locale) splits strings into word tokens using the ICU library’s Unicode word segmentation (UAX #29). For scripts without whitespace between words (for example Chinese, Japanese, and Thai) ICU applies dictionary-based segmentation, so such text is split into meaningful words. locale is the ICU locale passed to the segmenter (segmentation is mainly script- and dictionary-driven; the locale selects ICU’s locale-specific tailoring); it is mandatory and passed as a separate argument, for example tokens(value, 'icu', 'ja').
  • japanese splits Japanese text into words using the MeCab morphological analyzer. Requires a dictionary configured in the server configuration (see the text index documentation).
  • ngrams(N) splits strings into equally large N-grams (also see function ngrams). The ngram length can be specified using an optional integer parameter between 1 and 8, for example, tokens(value, 'ngrams', 3). The default ngram size, if not specified explicitly, is 3.
  • sparseGrams(min_length, max_length, min_cutoff_length) splits strings into variable-length n-grams of at least min_length and at most max_length (inclusive) characters (also see function sparseGrams). Unless specified explicitly, min_length and max_length default to 3 and 100. If parameter min_cutoff_length is provided, only n-grams with length greater or equal than min_cutoff_length are returned. Compared to ngrams(N), the sparseGrams tokenizer produces variable-length N-grams, allowing for a more flexible representation of the original text. For example, tokens(value, 'sparseGrams', 3, 5, 4) internally generates 3-, 4-, 5-grams from the input string but only the 4- and 5-grams are returned.
  • array performs no tokenization, i.e. every row value is a token (also see function array). For compatibility with other systems, keyword is available as an alias of array.

In case of the splitByString 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) -- 'splitByNonAlpha' tokenizer
tokens(value, 'splitByNonAlpha')
tokens(value, 'splitByString'[, separators])
tokens(value, 'splitByRegexp', regexp)
tokens(value, 'asciiCJK')
tokens(value, 'chinese'[, granularity])
tokens(value, 'icu', locale)
tokens(value, 'japanese')
tokens(value, 'ngrams'[, n])
tokens(value, 'sparseGrams'[, min_length, max_length[, min_cutoff_length]])
tokens(value, 'array')

Arguments

  • value — The input string. String or FixedString
  • tokenizer — The tokenizer to use. Valid arguments are splitByNonAlpha, splitByString, splitByRegexp, asciiCJK, chinese, icu, japanese, ngrams, sparseGrams, and array. Optional, if not set explicitly, defaults to splitByNonAlpha. const String
  • locale — Only relevant if argument tokenizer is icu: The mandatory locale, for example 'ja'. const String
  • n — Only relevant if argument tokenizer is ngrams: 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)
  • regexp — Only relevant if argument tokenizer is splitByRegexp: A mandatory parameter which defines the regular expression separator. const String
  • min_length — Only relevant if argument tokenizer is sparseGrams: An optional parameter which defines the minimum gram length, defaults to 3. const UInt8
  • max_length — Only relevant if argument tokenizer is sparseGrams: An optional parameter which defines the maximum gram length, defaults to 100. const UInt8
  • min_cutoff_length — Only relevant if argument tokenizer is sparseGrams: An optional parameter which defines the minimum cutoff length. const UInt8
  • granularity — Only relevant if argument tokenizer is chinese: An optional parameter, either coarse_grained (default) or fine_grained, controlling the segmentation granularity. const 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', 'ngrams', 3) AS tokens;
['abc','bc ','c d',' de','def']

tokensForLikePattern

Introduced in: v26.3.0

Splits a LIKE pattern string into tokens using the specified tokenizer.

Unlike the tokens function, this function is aware of LIKE pattern semantics (such as leading and trailing wildcard characters) and applies tokenizer-specific rules to extract meaningful tokens for pattern matching.

It supports the same argument sets as the tokens function, with some exceptions: the chinese and icu tokenizers are not supported here. Tokenization of LIKE patterns is only meaningful for tokenizers that explicitly opt into LIKE semantics (supportsStringLike()). Calling tokensForLikePattern with an unsupported tokenizer throws BAD_ARGUMENTS; use plain tokens instead.

Additional arguments after tokenizer are interpreted according to the selected tokenizer (for example, n for ngrams, separators for splitByString, and min_length / max_length [/ min_cutoff_length] for sparseGrams).

This function is primarily intended for debugging and testing purposes, and is used internally to analyze tokenization behavior for LIKE patterns.

Syntax

tokensForLikePattern(value[, tokenizer[, tokenizer_specific_arguments...]])

Arguments

  • value — The input string. String or FixedString
  • tokenizer — The tokenizer to use. Valid arguments are splitByNonAlpha, splitByString, asciiCJK, ngrams, sparseGrams, and array. Optional, if not set explicitly, defaults to splitByNonAlpha. const String
  • locale — Only relevant if argument tokenizer is icu: The mandatory locale, for example 'ja'. const String
  • n — Only relevant if argument tokenizer is ngrams: 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)
  • regexp — Only relevant if argument tokenizer is splitByRegexp: A mandatory parameter which defines the regular expression separator. const String
  • min_length — Only relevant if argument tokenizer is sparseGrams: An optional parameter which defines the minimum gram length, defaults to 3. const UInt8
  • max_length — Only relevant if argument tokenizer is sparseGrams: An optional parameter which defines the maximum gram length, defaults to 100. const UInt8
  • min_cutoff_length — Only relevant if argument tokenizer is sparseGrams: An optional parameter which defines the minimum cutoff length. const UInt8
  • granularity — Only relevant if argument tokenizer is chinese: An optional parameter, either coarse_grained (default) or fine_grained, controlling the segmentation granularity. const String

Returned value

Returns the resulting array of tokens from input string. Array

Examples

Default tokenizer

SELECT tokensForLikePattern('%test1,test2,test3%') AS tokens;
['test2']
Navigation