Functions for splitting strings

**Syntax**

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

**Arguments**

- `s` — The string to split. [`String`](/docs/sql-reference/data-types/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`](/docs/sql-reference/data-types/int-uint)

**Returned value**

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

**Examples**

**Usage example**

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

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

## tokens[​](#tokens "Direct link to 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](/docs/sql-reference/functions/splitting-merging-functions#splitByNonAlpha)).
- `splitByString(S)` splits strings along certain user-defined separator strings `S` (also see function [splitByString](/docs/sql-reference/functions/splitting-merging-functions#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 `[' ']`.
- `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.
- `ngrams(N)` splits strings into equally large `N`-grams (also see function [ngrams](/docs/sql-reference/functions/splitting-merging-functions#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](/docs/sql-reference/functions/string-functions#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](/docs/sql-reference/functions/array-functions#array)).

In case of the `splitByString` tokenizer, if the tokens do not form a [prefix code](https://en.wikipedia.org/wiki/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, 'asciiCJK')
tokens(value, 'ngrams'[, n])
tokens(value, 'sparseGrams'[, min_length, max_length[, min_cutoff_length]])
tokens(value, 'array')
```

**Arguments**

- `value` — The input string. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/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`](/docs/sql-reference/data-types/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`](/docs/sql-reference/data-types/int-uint)
- `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)`](/docs/sql-reference/data-types/array)
- `min_length` — Only relevant if argument `tokenizer` is `sparseGrams`: An optional parameter which defines the minimum gram length, defaults to 3. [`const UInt8`](/docs/sql-reference/data-types/int-uint)
- `max_length` — Only relevant if argument `tokenizer` is `sparseGrams`: An optional parameter which defines the maximum gram length, defaults to 100. [`const UInt8`](/docs/sql-reference/data-types/int-uint)
- `min_cutoff_length` — Only relevant if argument `tokenizer` is `sparseGrams`: An optional parameter which defines the minimum cutoff length. [`const UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the resulting array of tokens from input string. [`Array`](/docs/sql-reference/data-types/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[​](#tokensForLikePattern "Direct link to 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; 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`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/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`](/docs/sql-reference/data-types/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`](/docs/sql-reference/data-types/int-uint)
- `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)`](/docs/sql-reference/data-types/array)
- `min_length` — Only relevant if argument `tokenizer` is `sparseGrams`: An optional parameter which defines the minimum gram length, defaults to 3. [`const UInt8`](/docs/sql-reference/data-types/int-uint)
- `max_length` — Only relevant if argument `tokenizer` is `sparseGrams`: An optional parameter which defines the maximum gram length, defaults to 100. [`const UInt8`](/docs/sql-reference/data-types/int-uint)
- `min_cutoff_length` — Only relevant if argument `tokenizer` is `sparseGrams`: An optional parameter which defines the minimum cutoff length. [`const UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**