Full-text search with text indexes

All available tokenizers are listed in [system.tokenizers](/docs/operations/system-tables/tokenizers).

Note

The `splitByString` tokenizer applies the split separators left-to-right.
This can create ambiguities.
For example, the separator strings `['%21', '%']` will cause `%21abc` to be tokenized as `['abc']`, whereas switching both separators strings `['%', '%21']` will output `['21abc']`.
In the most cases, you want that matching prefers longer separators first.
This can generally be done by passing the separator strings in order of descending length.
If the separator strings happen to form a [prefix code](https://en.wikipedia.org/wiki/Prefix_code), they can be passed in arbitrary order.

To understand how a tokenizer split the input string, you can use the [tokens](/docs/sql-reference/functions/splitting-merging-functions#tokens) and [tokensForLikePattern](/docs/sql-reference/functions/splitting-merging-functions#tokensForLikePattern) functions:

Example:

```
SELECT tokens('abc def', 'ngrams', 3);
```

Result:

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

*Working with non-ASCII inputs.*
Text indexes can be built on top of text data in any language and character set.
For non-ASCII text, the `asciiCJK` tokenizer is recommended as it correctly handles Unicode word boundaries including CJK characters.
:::

**Preprocessor argument (optional)**. The preprocessor refers to an expression which is applied to the input string before tokenization.

Typical use cases for the preprocessor argument include

1. Lower/upper-casing, or case folding to enable case-insensitive matching, e.g., [lower](/docs/sql-reference/functions/string-functions#lower), [lowerUTF8](/docs/sql-reference/functions/string-functions#lowerUTF8), [caseFoldUTF8](/docs/sql-reference/functions/string-functions#caseFoldUTF8).
2. UTF-8 normalization, e.g. [normalizeUTF8NFC](/docs/sql-reference/functions/string-functions#normalizeUTF8NFC), [normalizeUTF8NFD](/docs/sql-reference/functions/string-functions#normalizeUTF8NFD), [normalizeUTF8NFKC](/docs/sql-reference/functions/string-functions#normalizeUTF8NFKC), [normalizeUTF8NFKD](/docs/sql-reference/functions/string-functions#normalizeUTF8NFKD), [normalizeUTF8NFKCCasefold](/docs/sql-reference/functions/string-functions#normalizeUTF8NFKCCasefold), [toValidUTF8](/docs/sql-reference/functions/string-functions#toValidUTF8).
3. Removing or transforming unwanted characters or substrings, such as accents e.g. [extractTextFromHTML](/docs/sql-reference/functions/string-functions#extractTextFromHTML), [substring](/docs/sql-reference/functions/string-functions#substring), [idnaEncode](/docs/sql-reference/functions/string-functions#idnaEncode), [translate](/docs/sql-reference/functions/string-replace-functions#translate), [removeDiacriticsUTF8](/docs/sql-reference/functions/string-functions#removeDiacriticsUTF8).

The preprocessor expression must transform an input value of type [String](/docs/sql-reference/data-types/string) or [FixedString](/docs/sql-reference/data-types/fixedstring) to a value of the same type.
If the text index was build on a column of type `Nullable(T)` or `LowCardinality(T)` column, then the preprocessor expression should accept nullable or low-cardinality values (i.e. not throw an exception).

Examples:

- `INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))`
- `INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = substringIndex(col, '\n', 1))`
- `INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(extractTextFromHTML(col)))`
- `INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = removeDiacriticsUTF8(caseFoldUTF8(col)))`

Also, the preprocessor expression must only reference the column or expression on top of which the text index is defined.

Examples:

- `INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = upper(lower(col)))`
- `INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(lower(col), lower(col)))`
- Not allowed: `INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))`

Using non-deterministic functions is disallowed.

Functions [hasToken](/docs/sql-reference/functions/string-search-functions#hasToken), [hasAllTokens](/docs/sql-reference/functions/string-search-functions#hasAllTokens) and [hasAnyTokens](/docs/sql-reference/functions/string-search-functions#hasAnyTokens) use the preprocessor to first transform the search term before tokenizing it.

For example,

```
CREATE TABLE table
(
    str String,
    INDEX idx(str) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(str))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, 'Foo');
```

is equivalent to:

```
CREATE TABLE table
(
    str String,
    INDEX idx(lower(str)) TYPE text(tokenizer = 'splitByNonAlpha')
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, lower('Foo'));
```

In this case, the preprocessor expression transforms the array elements individually.

Example:

```
CREATE TABLE table
(
    arr Array(String),
    INDEX idx arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(arr))

    -- This is not legal:
    INDEX idx_illegal arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = arraySort(arr))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(arr, 'foo');
```

To define a preprocessor in a text index on build [Map](/docs/sql-reference/data-types/map)-type columns, users need to decide if the index is
build on the map keys or values.

Example:

```
CREATE TABLE table
(
    map Map(String, String),
    INDEX idx mapKeys(map)  TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(mapKeys(map)))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(mapKeys(map), 'foo');
```

**Other arguments (optional)**.

Optional advanced parameters

The default values of the following advanced parameters will work well in virtually all situations.
We do not recommend changing them.

Optional parameter `dictionary_block_size` (default: 512) specifies the size of dictionary blocks in rows.

Optional parameter `dictionary_block_frontcoding_compression` (default: 1) specifies if the dictionary blocks use front coding as compression.

Optional parameter `posting_list_block_size` (default: 1048576) specifies the size of posting list blocks in rows.

Optional parameter `posting_list_codec` (default: `none`) specifies the codec for posting list:

- `none` - the posting lists are stored without additional compression.
- `bitpacking` - apply [differential (delta) coding](https://en.wikipedia.org/wiki/Delta_encoding), followed by [bit-packing](https://dev.to/madhav_baby_giraffe/bit-packing-the-secret-to-optimizing-data-storage-and-transmission-m70) (each within blocks of fixed-size). Slows down SELECT queries, not recommended at the moment.

*Index granularity.*
Text indexes are implemented within ClickHouse as a type of [skip indexes](/docs/engines/table-engines/mergetree-family/mergetree#skip-index-types).
However, unlike other skip indexes, text indexes use an infinite granularity (100 million).
This can be seen in the table definition of a text index.

Example:

```
CREATE TABLE table(
    k UInt64,
    s String,
    INDEX idx(s) TYPE text(tokenizer = ngrams(2)))
ENGINE = MergeTree()
ORDER BY k;

SHOW CREATE TABLE table;
```

Result: