String splitting functions provide powerful tools for dividing strings into arrays based on various criteria.

​
ClickHouse function reference

​
splitByChar

Splits a string into substrings separated by a specified character.

Syntax:

splitByChar(separator, s[, max_substrings])

Arguments:

  • separator (String): The separator character.
  • s (String): The string to split.
  • max_substrings (UInt64, optional): Maximum number of substrings to return. Default is 0 (return all substrings).

Returns:

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

Example:

SELECT
  splitByChar(',', 'carnitas,al_pastor,barbacoa,lengua');

Result:

| splitByChar(',', 'carnitas,al_pastor,barbacoa,lengua') |
|--------------------------------------------------------|
| ['carnitas','al_pastor','barbacoa','lengua']           |

In this example, we split a string of taco fillings into an array using a comma as the separator.

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 is empty

When max_substrings is 0 or omitted, the function returns as many substrings as possible.

​
splitByString

Splits a string into substrings separated by a specified string delimiter.

Syntax:

splitByString(separator, s[, max_substrings])

Arguments:

  • separator (String): The string separator.
  • s (String): The string to split.
  • max_substrings (UInt64, optional): Maximum number of substrings to return. Default is 0 (return all substrings).

Returns:

An array of selected substrings. (Array(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 s is empty while the separator is not empty
  • If separator is an empty string, it splits the string s into an array of single characters

Example:

SELECT
  splitByString(', ', 'beef, chicken, carnitas, barbacoa') AS taco_fillings;

Result:

| taco_fillings                                |
|----------------------------------------------|
| ['beef','chicken','carnitas','barbacoa']     |

In this example, we split a string of taco fillings into an array using ’, ’ as the separator.

Example with empty separator:

SELECT
  splitByString('', 'salsa') AS salsa_letters;

Result:

| salsa_letters       |
|---------------------|
| ['s','a','l','s']   |

When the separator is an empty string, the function splits the input string into individual characters.

Example with max_substrings:

SELECT
  splitByString(', ', 'mild, medium, hot, extra hot', 2) AS sauce_levels;

Result:

| sauce_levels      |
|-------------------|
| ['mild','medium'] |

By specifying max_substrings as 2, we limit the output to the first two substrings.

​
splitByRegexp

Splits a string into substrings using a regular expression as the separator.

Syntax:

splitByRegexp(regexp, s[, max_substrings])

Arguments:

  • regexp (String or FixedString): Regular expression used as the separator.
  • s (String): The string to split.
  • max_substrings (UInt64, optional): Maximum number of substrings to return. Default is 0 (return all substrings).

Returns:

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

Example:

SELECT
  splitByRegexp('\\s+', 'carnitas tacos al pastor barbacoa');

Result:

| splitByRegexp('\\s+', 'carnitas tacos al pastor barbacoa') |
|------------------------------------------------------------|
| ['carnitas','tacos','al','pastor','barbacoa']              |

In this example, the function splits the taco order into individual words using whitespace as the separator.

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

  2. If no match is found for the regular expression, the string s won’t be split.

Example with max_substrings:

SELECT
  splitByRegexp('\\s+', 'carnitas tacos al pastor barbacoa', 3);

Result:

| splitByRegexp('\\s+', 'carnitas tacos al pastor barbacoa', 3) |
|---------------------------------------------------------------|
| ['carnitas','tacos','al']                                     |

This function is useful for parsing structured text data, extracting specific patterns, or tokenizing strings based on complex separators.

​
splitByWhitespace

Splits a string into substrings separated by whitespace characters.

Syntax:

splitByWhitespace(s[, max_substrings])

Arguments:

  • s (String): The string to split.
  • max_substrings (UInt64, optional): The maximum number of substrings to return.
    • Default value: 0 (return all possible substrings)

Returns:

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

Example:

SELECT
  splitByWhitespace('Carne asada tacos al pastor') AS taco_types;

Result:

| taco_types                                 |
|--------------------------------------------|
| ['Carne','asada','tacos','al','pastor']    |

In this example, the function splits the taco order into individual words.

  1. Empty substrings are not included in the result.
  2. When max_substrings > 0, the function returns at most that many substrings.

Example with max_substrings:

SELECT
	splitByWhitespace('Carne asada tacos al pastor', 3) AS limited_taco_types;

Result:

| limited_taco_types         |
|----------------------------|
| ['Carne','asada','tacos']  |

This function is useful for parsing space-separated values or tokenizing text for further analysis.

​
splitByNonAlpha

Splits a string into substrings separated by non-alphabetic characters (whitespace and punctuation). Returns an array of selected substrings.

Syntax:

splitByNonAlpha(s[, max_substrings])

Arguments:

  • s (String): The string to split.
  • max_substrings (UInt64, optional): The maximum number of substrings to return. Default is 0 (return all substrings).

Returns:

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

Example:

SELECT
  splitByNonAlpha('Crunchy tacos: $3.99, Soft tacos: $3.50!');

Result:

| splitByNonAlpha('Crunchy tacos: $3.99, Soft tacos: $3.50!')      |
|------------------------------------------------------------------|
| ['Crunchy','tacos','3','99','Soft','tacos','3','50']             |

In this example, the function splits the taco menu string into an array of words and numbers, removing all non-alphabetic characters.

Example with max_substrings:

SELECT
	splitByNonAlpha('Crunchy tacos: $3.99, Soft tacos: $3.50!', 3) AS limited_taco_types;

Result:

| splitByNonAlpha('Crunchy tacos: $3.99, Soft tacos: $3.50!', 3)  |
|-----------------------------------------------------------------|
| ['Crunchy','tacos','3']                                         |

This function is useful for tokenizing text, extracting words from sentences, or parsing structured strings where non-alphabetic characters serve as delimiters.

​
arrayStringConcat

Concatenates string representations of values listed in the array with an optional separator.

Syntax:

arrayStringConcat(arr[, separator])

Arguments:

  • arr (Array): The input array of strings to concatenate.
  • separator (String, optional): The separator to use between array elements. Defaults to an empty string.

Returns:

A string containing the concatenated elements of the input array. (String)

Example:

SELECT
  arrayStringConcat(['Carne Asada', 'Al Pastor', 'Pollo'], ', ') AS taco_order;

Result:

| taco_order                      |
|---------------------------------|
| Carne Asada, Al Pastor, Pollo   |

In this example, arrayStringConcat joins the array of taco types into a single string, separating each type with a comma and space.

If the separator is omitted, the array elements are concatenated without any characters between them.

​
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 (String): The string to split.
  • max_substrings (UInt64, optional): Maximum number of substrings to return. Default is 0 (return all substrings).

Returns:

An array of selected substrings. [Array(String)]

Example:

SELECT
  alphaTokens('Crunchy Taco Supreme 2.99') AS taco_tokens;

Result:

| taco_tokens                     |
|---------------------------------|
| ['Crunchy','Taco','Supreme']    |

In this example, alphaTokens extracts all consecutive alphabetic substrings from the taco menu item, ignoring numbers and spaces.

​
extractAllGroups

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

Syntax:

extractAllGroups(text, regexp)

Arguments:

  • text (String or FixedString): The input text to search for matches.
  • regexp (String or FixedString): The regular expression to match against. Must be a constant.

Returns:

  • An array of arrays containing the matched groups. [Array(Array(String))]
    • If at least one matching group is found, returns an array of arrays, where each inner array represents a match and contains the captured groups.
    • If no matching groups are found, returns an empty array.

Example:

SELECT
	extractAllGroups('Carnitas Taco=$5.99, Fish Taco="$6.50"', '("[^"]+"|\\w+)=("[^"]+"|\\w+)') AS taco_prices;

Result:

| taco_prices                                         |
|-----------------------------------------------------|
| [['Carnitas Taco','5.99'],['Fish Taco','"$6.50"']]  |

In this example:

  • The function extracts price information for different types of tacos.
  • Each inner array contains two elements: the taco name and its price.
  • The regular expression matches patterns like name=price, where both name and price can be quoted or unquoted.

This function is particularly useful for parsing structured text data, such as log files or semi-structured data formats, where you need to extract multiple pieces of information based on a pattern.

​
ngrams

Splits a UTF-8 string into n-grams of a specified size.

Syntax:

ngrams(string, ngramsize)

Arguments:

  • string (String or FixedString): The input string to split into n-grams.
  • ngramsize (UInt): The size of each n-gram.

Returns:

An array of strings, where each string is an n-gram of the specified size. [Array(String)]

Example:

SELECT
  ngrams('Taco Tuesday', 3) AS taco_grams;

Result:

| taco_grams                                          |
|-----------------------------------------------------|
| ['Tac','aco',' Tu','Tue','ues','esd','sda','day']   |

In this example, the function splits the phrase “Taco Tuesday” into 3-grams. Each element in the resulting array represents a substring of 3 consecutive characters from the input string.

The function considers UTF-8 characters, so it works correctly with multi-byte characters as well.

This function is useful for text analysis, particularly in natural language processing tasks such as text classification or similarity measurement.

​
tokens

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

Syntax:

tokens(input_string)

Arguments:

  • input_string (String): The string to be tokenized.

Returns:

An array of tokens extracted from the input string. [Array(String)]

Example:

SELECT
  tokens('spicy_taco, mild_salsa; extra_guacamole') AS taco_ingredients;

Result:

| taco_ingredients                                       |
|--------------------------------------------------------|
| ['spicy','taco','mild','salsa','extra','guacamole']    |

In this example, the tokens function splits the input string into separate words, removing the non-alphanumeric separators (underscore, comma, semicolon, and space).

The function treats any sequence of non-alphanumeric ASCII characters as a separator. Multiple consecutive separators are treated as a single separator.

This function is useful for text processing tasks such as tokenizing ingredient lists, parsing user input, or preparing text for further analysis.

Was this page helpful?

Searching in StringsStrings