View source for Relational Data - Functions - Splitting Merging Functions

<span id="splitbychar"></span>
== splitByChar ==

Splits a string into substrings separated by a specified character. Uses a constant string <code>separator</code> 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'''

<syntaxhighlight lang="sql">splitByChar(separator, s[, max_substrings]))</syntaxhighlight>
'''Arguments'''

* <code>separator</code> — The separator which should contain exactly one character. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>s</code> — The string to split. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>max_substrings</code> — An optional <code>Int64</code> defaulting to 0. If <code>max_substrings</code> &gt; 0, the returned array will contain at most <code>max_substrings</code> substrings, otherwise the function will return as many substrings as possible.

'''Returned value(s)'''

* An array of selected substrings. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|String]]).

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 <code>s</code> is empty.

<div class="note">

The behavior of parameter <code>max_substrings</code> changed starting with ClickHouse v22.11. In versions older than that, <code>max_substrings &gt; 0</code> meant that <code>max_substring</code>-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: <code>SELECT splitByChar('=', 'a=b=c=d', 2);</code> returned <code>['a','b','c=d']</code> - in v22.11: <code>SELECT splitByChar('=', 'a=b=c=d', 2);</code> returned <code>['a','b']</code>

A behavior similar to ClickHouse pre-v22.11 can be achieved by setting [https://clickhouse.com/docs/en/operations/settings/settings#splitby_max_substrings_includes_remaining_string splitby_max_substrings_includes_remaining_string] <code>SELECT splitByChar('=', 'a=b=c=d', 2) SETTINGS splitby_max_substrings_includes_remaining_string = 1 -- ['a', 'b=c=d']</code>


</div>
'''Example'''

<syntaxhighlight lang="sql">SELECT splitByChar(',', '1,2,3,abcde');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─splitByChar(',', '1,2,3,abcde')─┐
│ ['1','2','3','abcde']           │
└─────────────────────────────────┘</syntaxhighlight>
<span id="splitbystring"></span>
== splitByString ==

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

'''Syntax'''

<syntaxhighlight lang="sql">splitByString(separator, s[, max_substrings]))</syntaxhighlight>
'''Arguments'''

* <code>separator</code> — The separator. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>s</code> — The string to split. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>max_substrings</code> — An optional <code>Int64</code> defaulting to 0. When <code>max_substrings</code> &gt; 0, the returned substrings will be no more than <code>max_substrings</code>, otherwise the function will return as many substrings as possible.

'''Returned value(s)'''

* An array of selected substrings. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|String]]).

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 <code>s</code> is empty while the separator is not empty.

<div class="note">

Setting [https://clickhouse.com/docs/en/operations/settings/settings#splitby_max_substrings_includes_remaining_string 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 <code>max_substrings</code> &gt; 0.


</div>
'''Example'''

<syntaxhighlight lang="sql">SELECT splitByString(', ', '1, 2 3, 4,5, abcde');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─splitByString(', ', '1, 2 3, 4,5, abcde')─┐
│ ['1','2 3','4,5','abcde']                 │
└───────────────────────────────────────────┘</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT splitByString('', 'abcde');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─splitByString('', 'abcde')─┐
│ ['a','b','c','d','e']      │
└────────────────────────────┘</syntaxhighlight>
<span id="splitbyregexp"></span>
== splitByRegexp ==

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

'''Syntax'''

<syntaxhighlight lang="sql">splitByRegexp(regexp, s[, max_substrings]))</syntaxhighlight>
'''Arguments'''

* <code>regexp</code> — Regular expression. Constant. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].
* <code>s</code> — The string to split. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>max_substrings</code> — An optional <code>Int64</code> defaulting to 0. When <code>max_substrings</code> &gt; 0, the returned substrings will be no more than <code>max_substrings</code>, otherwise the function will return as many substrings as possible.

'''Returned value(s)'''

* An array of selected substrings. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|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 <code>s</code> is empty while the regular expression is not empty.

<div class="note">

Setting [https://clickhouse.com/docs/en/operations/settings/settings#splitby_max_substrings_includes_remaining_string 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 <code>max_substrings</code> &gt; 0.


</div>
'''Example'''

<syntaxhighlight lang="sql">SELECT splitByRegexp('\\d+', 'a12bc23de345f');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─splitByRegexp('\\d+', 'a12bc23de345f')─┐
│ ['a','bc','de','f']                    │
└────────────────────────────────────────┘</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT splitByRegexp('', 'abcde');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─splitByRegexp('', 'abcde')─┐
│ ['a','b','c','d','e']      │
└────────────────────────────┘</syntaxhighlight>
<span id="splitbywhitespace"></span>
== splitByWhitespace ==

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

'''Syntax'''

<syntaxhighlight lang="sql">splitByWhitespace(s[, max_substrings]))</syntaxhighlight>
'''Arguments'''

* <code>s</code> — The string to split. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>max_substrings</code> — An optional <code>Int64</code> defaulting to 0. When <code>max_substrings</code> &gt; 0, the returned substrings will be no more than <code>max_substrings</code>, otherwise the function will return as many substrings as possible.

'''Returned value(s)'''

* An array of selected substrings. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|String]]).

<div class="note">

Setting [https://clickhouse.com/docs/en/operations/settings/settings#splitby_max_substrings_includes_remaining_string 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 <code>max_substrings</code> &gt; 0.


</div>
'''Example'''

<syntaxhighlight lang="sql">SELECT splitByWhitespace('  1!  a,  b.  ');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─splitByWhitespace('  1!  a,  b.  ')─┐
│ ['1!','a,','b.']                    │
└─────────────────────────────────────┘</syntaxhighlight>
<span id="splitbynonalpha"></span>
== splitByNonAlpha ==

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

'''Syntax'''

<syntaxhighlight lang="sql">splitByNonAlpha(s[, max_substrings]))</syntaxhighlight>
'''Arguments'''

* <code>s</code> — The string to split. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>max_substrings</code> — An optional <code>Int64</code> defaulting to 0. When <code>max_substrings</code> &gt; 0, the returned substrings will be no more than <code>max_substrings</code>, otherwise the function will return as many substrings as possible.

'''Returned value(s)'''

* An array of selected substrings. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|String]]).

<div class="note">

Setting [https://clickhouse.com/docs/en/operations/settings/settings#splitby_max_substrings_includes_remaining_string 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 <code>max_substrings</code> &gt; 0.


</div>
'''Example'''

<syntaxhighlight lang="sql">SELECT splitByNonAlpha('  1!  a,  b.  ');</syntaxhighlight>
<syntaxhighlight lang="text">┌─splitByNonAlpha('  1!  a,  b.  ')─┐
│ ['1','a','b']                     │
└───────────────────────────────────┘</syntaxhighlight>
<span id="arraystringconcat"></span>
== arrayStringConcat ==

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

'''Syntax'''

<syntaxhighlight lang="sql">arrayStringConcat(arr\[, separator\])</syntaxhighlight>
'''Example'''

<syntaxhighlight lang="sql">SELECT arrayStringConcat(['12/05/2021', '12:50:00'], ' ') AS DateString;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─DateString──────────┐
│ 12/05/2021 12:50:00 │
└─────────────────────┘</syntaxhighlight>
<span id="alphatokens"></span>
== alphaTokens ==

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

'''Syntax'''

<syntaxhighlight lang="sql">alphaTokens(s[, max_substrings]))</syntaxhighlight>
Alias: <code>splitByAlpha</code>

'''Arguments'''

* <code>s</code> — The string to split. [[Relational_Data_-_Data_Types_-_String|String]].
* <code>max_substrings</code> — An optional <code>Int64</code> defaulting to 0. When <code>max_substrings</code> &gt; 0, the returned substrings will be no more than <code>max_substrings</code>, otherwise the function will return as many substrings as possible.

'''Returned value(s)'''

* An array of selected substrings. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|String]]).

<div class="note">

Setting [https://clickhouse.com/docs/en/operations/settings/settings#splitby_max_substrings_includes_remaining_string 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 <code>max_substrings</code> &gt; 0.


</div>
'''Example'''

<syntaxhighlight lang="sql">SELECT alphaTokens('abca1abc');</syntaxhighlight>
<syntaxhighlight lang="text">┌─alphaTokens('abca1abc')─┐
│ ['abca','abc']          │
└─────────────────────────┘</syntaxhighlight>
<span id="extractallgroups"></span>
== extractAllGroups ==

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

'''Syntax'''

<syntaxhighlight lang="sql">extractAllGroups(text, regexp)</syntaxhighlight>
'''Arguments'''

* <code>text</code> — [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].
* <code>regexp</code> — Regular expression. Constant. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].

'''Returned values'''

* If the function finds at least one matching group, it returns <code>Array(Array(String))</code> column, clustered by group_id (1 to N, where N is number of capturing groups in <code>regexp</code>). If there is no matching group, it returns an empty array. [[Relational_Data_-_Data_Types_-_Array|Array]].

'''Example'''

<syntaxhighlight lang="sql">SELECT extractAllGroups('abc=123, 8="hkl"', '("[^"]+"|\\w+)=("[^"]+"|\\w+)');</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─extractAllGroups('abc=123, 8="hkl"', '("[^"]+"|\\w+)=("[^"]+"|\\w+)')─┐
│ [['abc','123'],['8','"hkl"']]                                         │
└───────────────────────────────────────────────────────────────────────┘</syntaxhighlight>
== ngrams ==

Splits a UTF-8 string into n-grams of <code>ngramsize</code> symbols.

'''Syntax'''

<syntaxhighlight lang="sql">ngrams(string, ngramsize)</syntaxhighlight>
'''Arguments'''

* <code>string</code> — String. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].
* <code>ngramsize</code> — The size of an n-gram. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt]].

'''Returned values'''

* Array with n-grams. [[Relational_Data_-_Data_Types_-_Array|Array]]([[Relational_Data_-_Data_Types_-_String|String]]).

'''Example'''

<syntaxhighlight lang="sql">SELECT ngrams('ClickHouse', 3);</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─ngrams('ClickHouse', 3)───────────────────────────┐
│ ['Cli','lic','ick','ckH','kHo','Hou','ous','use'] │
└───────────────────────────────────────────────────┘</syntaxhighlight>
== tokens ==

Splits a string into tokens using non-alphanumeric ASCII characters as separators.

'''Arguments'''

* <code>input_string</code> — Any set of bytes represented as the [[Relational_Data_-_Data_Types_-_String|String]] data type object.

'''Returned value'''

* The resulting array of tokens from input string. [[Relational_Data_-_Data_Types_-_Array|Array]].

'''Example'''

<syntaxhighlight lang="sql">SELECT tokens('test1,;\\ test2,;\\ test3,;\\   test4') AS tokens;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─tokens────────────────────────────┐
│ ['test1','test2','test3','test4'] │
└───────────────────────────────────┘</syntaxhighlight>
[[Category:Relational_Data]]