# String Search (/docs/reference/sql/functions/string-search)



This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See [SQL interface scope](/docs/reference/clickhouse-compatibility#service-interfaces).

countMatches [#countmatches]

Returns number of matches of a regular expression in a string.

:::note Version dependent behavior
The behavior of this function depends on the RawTree version:

* in versions \< v25.6, the function stops counting at the first empty match even if a pattern accepts.
* in versions >= 25.6, the function continues execution when an empty match occurs. The legacy behavior can be restored using setting `count_matches_stop_at_empty_match = true`;
  :::

**Syntax**

```sql
countMatches(haystack, pattern)
```

**Arguments**

* `haystack` — The string to search in. [`String`](/reference/data-types/string)
* `pattern` — Regular expression pattern. [`String`](/reference/data-types/string)

**Returned value**

Returns the number of matches found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Count digit sequences**

```sql title=Query
SELECT countMatches('hello 123 world 456 test', '[0-9]+')
```

```text title=Response
┌─countMatches('hello 123 world 456 test', '[0-9]+')─┐
│                                                  2 │
└────────────────────────────────────────────────────┘
```

*Introduced in version 21.1.0.*

countMatchesCaseInsensitive [#countmatchescaseinsensitive]

Like [`countMatches`](#countMatches) but performs case-insensitive matching.

**Syntax**

```sql
countMatchesCaseInsensitive(haystack, pattern)
```

**Arguments**

* `haystack` — The string to search in. [`String`](/reference/data-types/string)
* `pattern` — Regular expression pattern. [`const String`](/reference/data-types/string)

**Returned value**

Returns the number of matches found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Case insensitive count**

```sql title=Query
SELECT countMatchesCaseInsensitive('Hello HELLO world', 'hello')
```

```text title=Response
┌─countMatchesCaseInsensitive('Hello HELLO world', 'hello')─┐
│                                                         2 │
└───────────────────────────────────────────────────────────┘
```

*Introduced in version 21.1.0.*

countSubstrings [#countsubstrings]

Returns how often a substring `needle` occurs in a string `haystack`.

**Syntax**

```sql
countSubstrings(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — String in which the search is performed. [String](/reference/data-types/string) or [Enum](/reference/data-types/enum). - `needle` — Substring to be searched. [String](/reference/data-types/string). - `start_pos` — Position (1-based) in `haystack` at which the search starts. [UInt](/reference/data-types/int-uint). Optional.

**Returned value**

The number of occurrences. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT countSubstrings('aaaa', 'aa');
```

```text title=Response
┌─countSubstrings('aaaa', 'aa')─┐
│                             2 │
└───────────────────────────────┘
```

**With start\_pos argument**

```sql title=Query
SELECT countSubstrings('abc___abc', 'abc', 4);
```

```text title=Response
┌─countSubstrings('abc___abc', 'abc', 4)─┐
│                                      1 │
└────────────────────────────────────────┘
```

*Introduced in version 21.1.0.*

countSubstringsCaseInsensitive [#countsubstringscaseinsensitive]

Like [`countSubstrings`](#countSubstrings) but counts case-insensitively.

**Syntax**

```sql
countSubstringsCaseInsensitive(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns the number of occurrences of the neddle in the haystack. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT countSubstringsCaseInsensitive('AAAA', 'aa');
```

```text title=Response
┌─countSubstri⋯AAA', 'aa')─┐
│                        2 │
└──────────────────────────┘
```

**With start\_pos argument**

```sql title=Query
SELECT countSubstringsCaseInsensitive('abc___ABC___abc', 'abc', 4);
```

```text title=Response
┌─countSubstri⋯, 'abc', 4)─┐
│                        2 │
└──────────────────────────┘
```

*Introduced in version 21.1.0.*

countSubstringsCaseInsensitiveUTF8 [#countsubstringscaseinsensitiveutf8]

Like [`countSubstrings`](#countSubstrings) but counts case-insensitively and assumes that haystack is a UTF-8 string.

**Syntax**

```sql
countSubstringsCaseInsensitiveUTF8(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — UTF-8 string in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns the number of occurrences of the needle in the haystack. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА');
```

```text title=Response
┌─countSubstri⋯шка', 'КА')─┐
│                        4 │
└──────────────────────────┘
```

**With start\_pos argument**

```sql title=Query
SELECT countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА', 13);
```

```text title=Response
┌─countSubstri⋯, 'КА', 13)─┐
│                        2 │
└──────────────────────────┘
```

*Introduced in version 21.1.0.*

extract [#extract]

Extracts the first match of a regular expression in a string.
If 'haystack' doesn't match 'pattern', an empty string is returned.

This function uses the RE2 regular expression library. Please refer to [re2](https://github.com/google/re2/wiki/Syntax) for supported syntax.

If the regular expression has capturing groups (sub-patterns), the function matches the input string against the first capturing group.

**Syntax**

```sql
extract(haystack, pattern)
```

**Arguments**

* `haystack` — String from which to extract. [`String`](/reference/data-types/string)
* `pattern` — Regular expression, typically containing a capturing group. [`const String`](/reference/data-types/string)

**Returned value**

Returns extracted fragment as a string. [`String`](/reference/data-types/string)

**Examples**

**Extract domain from email**

```sql title=Query
SELECT extract('test@clickhouse.com', '.*@(.*)$')
```

```text title=Response
┌─extract('test@clickhouse.com', '.*@(.*)$')─┐
│ clickhouse.com                             │
└────────────────────────────────────────────┘
```

**No match returns empty string**

```sql title=Query
SELECT extract('test@clickhouse.com', 'no_match')
```

```text title=Response
┌─extract('test@clickhouse.com', 'no_match')─┐
│                                            │
└────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

extractAll [#extractall]

Like [`extract`](#extract), but returns an array of all matches of a regular expression in a string.
If 'haystack' doesn't match the 'pattern' regex, an empty array is returned.

If the regular expression has capturing groups (sub-patterns), the function matches the input string against the first capturing group.

**Syntax**

```sql
extractAll(haystack, pattern)
```

**Arguments**

* `haystack` — String from which to extract fragments. [`String`](/reference/data-types/string)
* `pattern` — Regular expression, optionally containing capturing groups. [`const String`](/reference/data-types/string)

**Returned value**

Returns array of extracted fragments. [`Array(String)`](/reference/data-types/array)

**Examples**

**Extract all numbers**

```sql title=Query
SELECT extractAll('hello 123 world 456', '[0-9]+')
```

```text title=Response
┌─extractAll('hello 123 world 456', '[0-9]+')─┐
│ ['123','456']                               │
└─────────────────────────────────────────────┘
```

**Extract using capturing group**

```sql title=Query
SELECT extractAll('test@example.com, user@domain.org', '([a-zA-Z0-9]+)@')
```

```text title=Response
┌─extractAll('test@example.com, user@domain.org', '([a-zA-Z0-9]+)@')─┐
│ ['test','user']                                                    │
└────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

extractAllGroupsHorizontal [#extractallgroupshorizontal]

Matches all groups of a string using the provided regular expression and returns an array of arrays, where each array contains all captures from the same capturing group, organized by group number.

**Syntax**

```sql
extractAllGroupsHorizontal(s, regexp)
```

**Arguments**

* `s` — Input string to extract from. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `regexp` — Regular expression to match by. [`const String`](/reference/data-types/string) or [`const FixedString`](/reference/data-types/fixedstring)

**Returned value**

Returns an array of arrays, where each inner array contains all captures from one capturing group across all matches. The first inner array contains all captures from group 1, the second from group 2, etc. If no matches are found, returns an empty array. [`Array(Array(String))`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query
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 extractAllGroupsHorizontal(s, '< ([\\w\\-]+): ([^\\r\\n]+)');
```

```text title=Response
[['Server','Date','Content-Type','Connection'],['nginx','Tue, 22 Jan 2019 00:26:14 GMT','text/html; charset=UTF-8','keep-alive']]
```

*Introduced in version 20.5.0.*

extractGroups [#extractgroups]

Extracts the capturing groups from the first substring matched by a regular expression. To extract groups from all matches, use [`extractAllGroupsHorizontal`](#extractAllGroupsHorizontal) or [`extractAllGroupsVertical`](/reference/functions/regular-functions/splitting-merging-functions#extractAllGroupsVertical).

**Syntax**

```sql
extractGroups(s, regexp)
```

**Arguments**

* `s` — Input string to extract from. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `regexp` — Regular expression. Must contain at least one capturing group. Constant. [`const String`](/reference/data-types/string) or [`const FixedString`](/reference/data-types/fixedstring)

**Returned value**

If the regular expression matches, returns an array containing the captured groups (`1` to `N`, where `N` is the number of capturing groups in `regexp`) of the first match. If there is no match, returns an empty array. [`Array(String)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query
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 extractGroups(s, '< ([\\w\\-]+): ([^\\r\\n]+)');
```

```text title=Response
['Server','nginx']
```

*Introduced in version 20.5.0.*

hasAllTokens [#hasalltokens]

Like [`hasAnyTokens`](#hasAnyTokens), but returns 1, if all tokens in the `needle` string or array match the `input` string, and 0 otherwise. If `input` is a column, returns all rows that satisfy this condition.

:::note
Column `input` should have a [text index](/reference/engines/table-engines/mergetree-family/textindexes) defined for optimal performance.
If no text index is defined, the function performs a brute-force column scan which is orders of magnitude slower than an index lookup.
:::

Prior to searching, the function tokenizes

* the `input` argument (always), and
* the `needle` argument (if given as a [String](/reference/data-types/string))
  using the tokenizer specified for the text index.
  If the column has no text index defined, the `splitByNonAlpha` tokenizer is used instead.
  If the `needle` argument is of type [Array(String)](/reference/data-types/array), each array element is treated as a token — no additional tokenization takes place.
  If the text index has a [preprocessor](/reference/engines/table-engines/mergetree-family/textindexes#preprocessor-argument-optional) expression configured, the preprocessor is applied to the needle (if given as a `String`) before tokenization.
  If the text index has a [postprocessor](/reference/engines/table-engines/mergetree-family/textindexes#postprocessor-argument-optional) expression configured, the postprocessor is applied to needle tokens and the input tokens (i.e. both after tokenization).

Duplicate tokens are ignored.
For example, needles = \['ClickHouse', 'ClickHouse'] is treated the same as \['ClickHouse'].

:::note
When a text index defines a [preprocessor](/reference/engines/table-engines/mergetree-family/textindexes#creating-a-text-index) (for example `lowerUTF8`), `hasAllTokens` applies it to `input` and, when `needles` is a [String](/reference/data-types/string), to `needles` before tokenization. When `needles` is an [Array(String)](/reference/data-types/array), its elements are passed through as-is and the preprocessor is not applied to them.
The preprocessor is only applied on the text index path, so results may differ between queries that use the text index and queries that do not (e.g. `SETTINGS use_skip_indexes = 0`).
This inconsistency is tolerated to improve the usability of full-text search.
:::

**Syntax**

```sql
hasAllTokens(input, needles[, tokenizer])
```

**Arguments**

* `input` — The input column. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Nullable(String)`](/reference/data-types/nullable) or [`Nullable(FixedString)`](/reference/data-types/nullable) or [`Array(String)`](/reference/data-types/array) or [`Array(FixedString)`](/reference/data-types/array) or [`Array(Nullable(String))`](/reference/data-types/array) or [`Array(Nullable(FixedString))`](/reference/data-types/array)
* `needles` — Tokens to be searched. [`String`](/reference/data-types/string) or [`Array(String)`](/reference/data-types/array)
* `tokenizer` — The tokenizer to use. Valid arguments are `splitByNonAlpha`, `splitByString`, `splitByRegexp`, `asciiCJK`, `chinese`, `icu('<locale>')`, `japanese`, `standard`, `ngrams`, `sparseGrams`, and `array`. Optional, if not set explicitly, defaults to `splitByNonAlpha`. [`const String`](/reference/data-types/string)

**Returned value**

Returns 1, if all needles match. 0, otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Basic usage with a string needle**

```sql title=Query
CREATE TABLE doc (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO doc VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM doc WHERE hasAllTokens(msg, 'a\\d()');
```

```text title=Response
┌─count()─┐
│       1 │
└─────────┘
```

**Specify needles to be searched for AS-IS (no tokenization) in an array**

```sql title=Query
CREATE TABLE doc (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO doc VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM doc WHERE hasAllTokens(msg, ['a', 'd']);
```

```text title=Response
┌─count()─┐
│       1 │
└─────────┘
```

**Generate needles using the `tokens` function**

```sql title=Query
CREATE TABLE doc (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO doc VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM doc WHERE hasAllTokens(msg, tokens('a()d', 'splitByString', ['()', '\\']));
```

```text title=Response
┌─count()─┐
│       1 │
└─────────┘
```

**Use a custom tokenizer via the 3rd argument**

```sql title=Query
SELECT hasAllTokens('abcdef', 'abc', 'ngrams(3)');
```

```text title=Response
┌─hasAllTokens⋯ngrams(3)')─┐
│                        1 │
└──────────────────────────┘
```

**Usage examples for array and map columns**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});
```

**Example with an array column**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

SELECT count() FROM log WHERE hasAllTokens(tags, 'clickhouse');
```

```text title=Response
┌─count()─┐
│       1 │
└─────────┘
```

**Example with mapKeys**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

SELECT count() FROM log WHERE hasAllTokens(mapKeys(attributes), ['address', 'log_level']);
```

```text title=Response
┌─count()─┐
│       1 │
└─────────┘
```

**Example with mapValues**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

SELECT count() FROM log WHERE hasAllTokens(mapValues(attributes), ['192.0.0.1', 'DEBUG']);
```

```text title=Response
┌─count()─┐
│       0 │
└─────────┘
```

*Introduced in version 25.10.0.*

hasAnyTokens [#hasanytokens]

Returns 1, if at least one token in the `needle` string or array matches the `input` string, and 0 otherwise. If `input` is a column, returns all rows that satisfy this condition.

:::note
Column `input` should have a [text index](/reference/engines/table-engines/mergetree-family/textindexes) defined for optimal performance.
If no text index is defined, the function performs a brute-force column scan which is orders of magnitude slower than an index lookup.
:::

Prior to searching, the function tokenizes

* the `input` argument (always), and
* the `needle` argument (if given as a [String](/reference/data-types/string))
  using the tokenizer specified for the text index.
  If the column has no text index defined, the `splitByNonAlpha` tokenizer is used instead.
  If the `needle` argument is of type [Array(String)](/reference/data-types/array), each array element is treated as a token — no additional tokenization takes place.
  If the text index has a [preprocessor](/reference/engines/table-engines/mergetree-family/textindexes#preprocessor-argument-optional) expression configured, the preprocessor is applied to the needle (if given as a `String`) before tokenization.
  If the text index has a [postprocessor](/reference/engines/table-engines/mergetree-family/textindexes#postprocessor-argument-optional) expression configured, the postprocessor is applied to needle tokens and the input tokens (i.e. both after tokenization).

Duplicate tokens are ignored.
For example, \['ClickHouse', 'ClickHouse'] is treated the same as \['ClickHouse'].

:::note
When a text index defines a [preprocessor](/reference/engines/table-engines/mergetree-family/textindexes#creating-a-text-index) (for example `lowerUTF8`), `hasAnyTokens` applies it to `input` and, when `needles` is a [String](/reference/data-types/string), to `needles` before tokenization. When `needles` is an [Array(String)](/reference/data-types/array), its elements are passed through as-is and the preprocessor is not applied to them.
The preprocessor is only applied on the text index path, so results may differ between queries that use the text index and queries that do not (e.g. `SETTINGS use_skip_indexes = 0`).
This inconsistency is tolerated to improve the usability of full-text search.
:::

**Syntax**

```sql
hasAnyTokens(input, needles[, tokenizer])
```

**Arguments**

* `input` — The input column. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Nullable(String)`](/reference/data-types/nullable) or [`Nullable(FixedString)`](/reference/data-types/nullable) or [`Array(String)`](/reference/data-types/array) or [`Array(FixedString)`](/reference/data-types/array) or [`Array(Nullable(String))`](/reference/data-types/array) or [`Array(Nullable(FixedString))`](/reference/data-types/array)
* `needles` — Tokens to be searched. [`String`](/reference/data-types/string) or [`Array(String)`](/reference/data-types/array)
* `tokenizer` — The tokenizer to use. Valid arguments are `splitByNonAlpha`, `splitByString`, `splitByRegexp`, `asciiCJK`, `chinese`, `icu('<locale>')`, `japanese`, `standard`, `ngrams`, `sparseGrams`, and `array`. Optional, if not set explicitly, defaults to `splitByNonAlpha`. [`const String`](/reference/data-types/string)

**Returned value**

Returns `1`, if there was at least one match. `0`, otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Basic usage with a string needle**

```sql title=Query
CREATE TABLE doc (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO doc VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM doc WHERE hasAnyTokens(msg, 'a\\d()');
```

```text title=Response
┌─count()─┐
│       3 │
└─────────┘
```

**Specify needles to be searched for AS-IS (no tokenization) in an array**

```sql title=Query
CREATE TABLE doc (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO doc VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM doc WHERE hasAnyTokens(msg, ['a', 'd']);
```

```text title=Response
┌─count()─┐
│       3 │
└─────────┘
```

**Generate needles using the `tokens` function**

```sql title=Query
CREATE TABLE doc (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO doc VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM doc WHERE hasAnyTokens(msg, tokens('a()d', 'splitByString', ['()', '\\']));
```

```text title=Response
┌─count()─┐
│       3 │
└─────────┘
```

**Usage examples for array and map columns**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});
```

**Example with an array column**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

SELECT count() FROM log WHERE hasAnyTokens(tags, 'clickhouse');
```

```text title=Response
┌─count()─┐
│       1 │
└─────────┘
```

**Example with mapKeys**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

SELECT count() FROM log WHERE hasAnyTokens(mapKeys(attributes), ['address', 'log_level']);
```

```text title=Response
┌─count()─┐
│       2 │
└─────────┘
```

**Example with mapValues**

```sql title=Query
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

SELECT count() FROM log WHERE hasAnyTokens(mapValues(attributes), ['192.0.0.1', 'DEBUG']);
```

```text title=Response
┌─count()─┐
│       2 │
└─────────┘
```

*Introduced in version 25.10.0.*

hasPhrase [#hasphrase]

Checks if the `input` contains all tokens from the `phrase` in consecutive order.

:::note
Column `input` should have a [text index](/reference/engines/table-engines/mergetree-family/textindexes) defined for optimal performance.
If no text index is defined, the function performs a brute-force column scan which is orders of magnitude slower than an index lookup.
:::

Prior to searching, the function tokenizes both the `input` and the `phrase` arguments using the tokenizer specified for the text index.
If the column has no text index defined, the `splitByNonAlpha` tokenizer is used instead — unless a tokenizer is provided as the optional third argument.
The tokenizer argument must be one of `splitByNonAlpha`, `splitByString`, `splitByRegexp`, `ngrams`, `asciiCJK`, `icu`, or `standard`.
Note that `splitByRegexp` is not supported for `hasPhrase` when the text index also defines a postprocessor.

:::note
When a text index defines a [preprocessor](/reference/engines/table-engines/mergetree-family/textindexes#creating-a-text-index) (for example `lowerUTF8`), `hasPhrase` applies it to both `input` and `phrase` before tokenization.
The preprocessor is only applied on the text index path, so results may differ between queries that use the text index and queries that do not (e.g. `SETTINGS use_skip_indexes = 0`).
This inconsistency is tolerated to improve the usability of full-text search.
:::

Unlike [`hasToken`](#hasToken), [`hasAnyTokens`](#hasAnyTokens) and [`hasAllTokens`](#hasAllTokens), `hasPhrase` requires the tokens to appear in the same order
and without any intervening tokens. For example, `hasPhrase('the quick brown fox', 'quick fox')` returns 0
because "brown" appears between "quick" and "fox".

**Syntax**

```sql
hasPhrase(input, phrase[, tokenizer])
```

**Arguments**

* `input` — The input column. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `phrase` — Phrase to search for. [`const String`](/reference/data-types/string)
* `tokenizer` — The tokenizer to use. Optional, defaults to `splitByNonAlpha`. [`const String`](/reference/data-types/string)

**Returned value**

Returns `1` if the phrase is found as a consecutive token sequence, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Phrase match**

```sql title=Query
SELECT hasPhrase('the quick brown fox jumps', 'quick brown')
```

```text title=Response
┌─hasPhrase('the quick brown fox jumps', 'quick brown')─┐
│                                                     1 │
└───────────────────────────────────────────────────────┘
```

**Non-consecutive tokens**

```sql title=Query
SELECT hasPhrase('the quick brown fox jumps', 'quick fox')
```

```text title=Response
┌─hasPhrase('the quick brown fox jumps', 'quick fox')─┐
│                                                   0 │
└─────────────────────────────────────────────────────┘
```

*Introduced in version 26.4.0.*

hasSubsequence [#hassubsequence]

Checks if a needle is a subsequence of a haystack.
A subsequence of a string is a sequence that can be derived from another string by deleting some or no characters without changing the order of the remaining characters.

**Syntax**

```sql
hasSubsequence(haystack, needle)
```

**Arguments**

* `haystack` — String in which to search for the subsequence. [`String`](/reference/data-types/string)
* `needle` — Subsequence to be searched. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if needle is a subsequence of haystack, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Basic subsequence check**

```sql title=Query
SELECT hasSubsequence('Hello World', 'HlWrd')
```

```text title=Response
┌─hasSubsequence('Hello World', 'HlWrd')─┐
│                                      1 │
└────────────────────────────────────────┘
```

**No subsequence found**

```sql title=Query
SELECT hasSubsequence('Hello World', 'xyz')
```

```text title=Response
┌─hasSubsequence('Hello World', 'xyz')─┐
│                                    0 │
└──────────────────────────────────────┘
```

*Introduced in version 23.7.0.*

hasSubsequenceCaseInsensitive [#hassubsequencecaseinsensitive]

Like [`hasSubsequence`](#hasSubsequence) but searches case-insensitively.

**Syntax**

```sql
hasSubsequenceCaseInsensitive(haystack, needle)
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Subsequence to be searched. [`String`](/reference/data-types/string)

**Returned value**

Returns 1, if needle is a subsequence of haystack, 0 otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT hasSubsequenceCaseInsensitive('garbage', 'ARG');
```

```text title=Response
┌─hasSubsequenceCaseInsensitive('garbage', 'ARG')─┐
│                                               1 │
└─────────────────────────────────────────────────┘
```

*Introduced in version 23.7.0.*

hasSubsequenceCaseInsensitiveUTF8 [#hassubsequencecaseinsensitiveutf8]

Like [`hasSubsequenceUTF8`](#hasSubsequenceUTF8) but searches case-insensitively.

**Syntax**

```sql
hasSubsequenceCaseInsensitiveUTF8(haystack, needle)
```

**Arguments**

* `haystack` — UTF8-encoded string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — UTF8-encoded subsequence string to be searched. [`String`](/reference/data-types/string)

**Returned value**

Returns 1, if needle is a subsequence of haystack, 0 otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT hasSubsequenceCaseInsensitiveUTF8('ClickHouse - столбцовая система управления базами данных', 'СИСТЕМА');
```

```text title=Response
┌─hasSubsequen⋯ 'СИСТЕМА')─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 23.7.0.*

hasSubsequenceUTF8 [#hassubsequenceutf8]

Like [`hasSubsequence`](/reference/functions/regular-functions/string-search-functions#hasSubsequence) but assumes haystack and needle are UTF-8 encoded strings.

**Syntax**

```sql
hasSubsequenceUTF8(haystack, needle)
```

**Arguments**

* `haystack` — The string in which to search. [`String`](/reference/data-types/string)
* `needle` — The subsequence to search for. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if `needle` is a subsequence of `haystack`, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT hasSubsequenceUTF8('картошка', 'кошка');
```

```text title=Response
┌─hasSubsequen⋯', 'кошка')─┐
│                        1 │
└──────────────────────────┘
```

**Non-matching subsequence**

```sql title=Query
SELECT hasSubsequenceUTF8('картошка', 'апельсин');
```

```text title=Response
┌─hasSubsequen⋯'апельсин')─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 23.7.0.*

hasToken [#hastoken]

Checks if the given token is present in the haystack.

Uses [splitByNonAlpha](/reference/functions/regular-functions/splitting-merging-functions#splitByNonAlpha) as tokenizer, i.e. a token is defined as the longest possible sub-sequence of consecutive characters `[0-9A-Za-z_]` (numbers, ASCII characters and underscore).

**Syntax**

```sql
hasToken(haystack, token)
```

**Arguments**

* `haystack` — String to be searched. [`String`](/reference/data-types/string)
* `token` — Token to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns `1` if the token is found, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Token search**

```sql title=Query
SELECT hasToken('clickhouse test', 'test')
```

```text title=Response
┌─hasToken('clickhouse test', 'test')─┐
│                                   1 │
└─────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

hasTokenCaseInsensitive [#hastokencaseinsensitive]

Performs case insensitive lookup of needle in haystack using tokenbf\_v1 index.

:::note
This function has certain pitfalls with non-default tokenizers and preprocessor or postprocessor expressions.
We recommend using [`hasAnyTokens`](#hasAnyTokens) and [`hasAllTokens`](#hasAllTokens) instead.
:::

**Syntax**

```sql
hasTokenCaseInsensitive(haystack, needle)
```

*Introduced in version 20.1.0.*

hasTokenCaseInsensitiveOrNull [#hastokencaseinsensitiveornull]

Performs case insensitive lookup of needle in haystack using tokenbf\_v1 index. Returns null if needle is ill-formed.

:::note
This function has certain pitfalls with non-default tokenizers and preprocessor or postprocessor expressions.
We recommend using [`hasAnyTokens`](#hasAnyTokens) and [`hasAllTokens`](#hasAllTokens) instead.
:::

**Syntax**

```sql
hasTokenCaseInsensitiveOrNull(haystack, needle)
```

*Introduced in version 23.1.0.*

hasTokenOrNull [#hastokenornull]

Like [`hasToken`](#hasToken) but returns null if token is ill-formed.

**Syntax**

```sql
hasTokenOrNull(haystack, token)
```

**Arguments**

* `haystack` — String to be searched. Must be constant. [`String`](/reference/data-types/string)
* `token` — Token to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns `1` if the token is found, `0` otherwise, null if token is ill-formed. [`Nullable(UInt8)`](/reference/data-types/nullable)

**Examples**

**Usage example**

```sql title=Query
SELECT hasTokenOrNull('apple banana cherry', 'ban ana');
```

```text title=Response
┌─hasTokenOrNu⋯ 'ban ana')─┐
│                     ᴺᵁᴸᴸ │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

highlight [#highlight]

Highlights occurrences of search terms in a text string by wrapping them with HTML tags.

The function performs ASCII case-insensitive matching. If multiple search terms overlap or are adjacent in the text, the matched regions are merged into a single highlighted span.

**Syntax**

```sql
highlight(haystack, needles[, open_tag, close_tag])
```

**Arguments**

* `haystack` — The text to search in. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `needles` — An array of search terms to highlight. [`const Array(String)`](/reference/data-types/array)
* `open_tag` — The opening tag to insert before each match. Default: `<em>`. [`const String`](/reference/data-types/string)
* `close_tag` — The closing tag to insert after each match. Default: `</em>`. [`const String`](/reference/data-types/string)

**Returned value**

Returns the input text with matched terms wrapped in the specified tags. [`String`](/reference/data-types/string)

**Examples**

**Basic highlight**

```sql title=Query
SELECT highlight('The quick brown fox', ['quick', 'fox'])
```

```text title=Response
┌─highlight('The quick brown fox', ['quick', 'fox'])─┐
│ The <em>quick</em> brown <em>fox</em>              │
└────────────────────────────────────────────────────┘
```

**Custom tags**

```sql title=Query
SELECT highlight('Hello World', ['hello'], '<b>', '</b>')
```

```text title=Response
┌─highlight('Hello World', ['hello'], '<b>', '</b>')─┐
│ <b>Hello</b> World                                 │
└────────────────────────────────────────────────────┘
```

*Introduced in version 26.4.0.*

ilike [#ilike]

Like [`like`](#like) but searches case-insensitively. Supports the optional `ESCAPE` clause (see `like`).

**Syntax**

```sql
ilike(haystack, pattern[, escape_character])
-- haystack ILIKE pattern [ESCAPE 'escape_character']
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `pattern` — LIKE pattern to match against. [`String`](/reference/data-types/string)
* `escape_character` — Optional single-character string to use as the escape character instead of `\`. Default: `\`. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the string matches the LIKE pattern (case-insensitive), otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT ilike('ClickHouse', '%house%');
```

```text title=Response
┌─ilike('ClickHouse', '%house%')─┐
│                              1 │
└────────────────────────────────┘
```

*Introduced in version 20.6.0.*

like [#like]

Returns whether string `haystack` matches the `LIKE` expression `pattern`.

A `LIKE` expression can contain normal characters and the following metasymbols:

* `%` indicates an arbitrary number of arbitrary characters (including zero characters).
* `_` indicates a single arbitrary character.
* `\` is for escaping literals `%`, `_` and `\`.

Matching is based on UTF-8, e.g. `_` matches the Unicode code point `¥` which is represented in UTF-8 using two bytes.

If the haystack or the `LIKE` expression are not valid UTF-8, the behavior is undefined.

No automatic Unicode normalization is performed, you can use the `normalizeUTF8*` functions for that.

To match against literal `%`, `_` and `\` (which are `LIKE` metacharacters), prepend them with a backslash: `\%`, `\_` and `\\`.
The backslash loses its special meaning (i.e. is interpreted literally) if it prepends a character different than `%`, `_` or `\`.

:::note
RawTree requires backslashes in strings [to be quoted as well](/reference/syntax#string), so you would actually need to write `\\%`, `\\_` and `\\\\`.
:::

For `LIKE` expressions of the form `%needle%`, the function is as fast as the `position` function.
All other LIKE expressions are internally converted to a regular expression and executed with a performance similar to function `match`.

ESCAPE clause [#escape-clause]

The optional `ESCAPE` clause specifies a custom escape character (must be a single ASCII character).
When provided, the custom escape character replaces the default backslash for escaping `%` and `_` metacharacters.
The escape character can escape three things: `%` (literal percent), `_` (literal underscore), and itself (literal escape character).
When a custom escape character is used, the backslash has no special meaning and is treated as a literal character.

**Syntax**

```sql
like(haystack, pattern[, escape_character])
-- haystack LIKE pattern [ESCAPE 'escape_character']
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `pattern` — `LIKE` pattern to match against. Can contain `%` (matches any number of characters), `_` (matches single character), and `\` for escaping. [`String`](/reference/data-types/string)
* `escape_character` — Optional single-character string to use as the escape character instead of `\`. Default: `\`. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the string matches the `LIKE` pattern, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT like('ClickHouse', '%House');
```

```text title=Response
┌─like('ClickHouse', '%House')─┐
│                            1 │
└──────────────────────────────┘
```

**Single character wildcard**

```sql title=Query
SELECT like('ClickHouse', 'Click_ouse');
```

```text title=Response
┌─like('ClickH⋯lick_ouse')─┐
│                        1 │
└──────────────────────────┘
```

**Non-matching pattern**

```sql title=Query
SELECT like('ClickHouse', '%SQL%');
```

```text title=Response
┌─like('ClickHouse', '%SQL%')─┐
│                           0 │
└─────────────────────────────┘
```

**ESCAPE clause**

```sql title=Query
SELECT '50%off' LIKE '50#%off' ESCAPE '#';
```

```text title=Response
┌─like('50%off', '50#%off', '#')─┐
│                              1 │
└────────────────────────────────┘
```

*Introduced in version 1.1.0.*

locate [#locate]

Like [`position`](#position) but with arguments `haystack` and `locate` switched.

:::note Version dependent behavior
The behavior of this function depends on the RawTree version:

* in versions \< v24.3, `locate` was an alias of function `position` and accepted arguments `(haystack, needle[, start_pos])`.
* in versions >= 24.3, `locate` is an individual function (for better compatibility with MySQL) and accepts arguments `(needle, haystack[, start_pos])`.
  The previous behavior can be restored using setting `function_locate_has_mysql_compatible_argument_order = false`.
  :::

**Syntax**

```sql
locate(needle, haystack[, start_pos])
```

**Arguments**

* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt`](/reference/data-types/int-uint)

**Returned value**

Returns starting position in bytes and counting from 1, if the substring was found, `0`, if the substring was not found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query
SELECT locate('ca', 'abcabc')
```

```text title=Response
┌─locate('ca', 'abcabc')─┐
│                      3 │
└────────────────────────┘
```

*Introduced in version 18.16.0.*

match [#match]

Checks if a provided string matches the provided regular expression pattern.

This function uses the RE2 regular expression library. Please refer to [re2](https://github.com/google/re2/wiki/Syntax) for supported syntax.

Matching works under UTF-8 assumptions, e.g. `¥` uses two bytes internally but matching treats it as a single codepoint.
The regular expression must not contain NULL bytes.
If the haystack or the pattern are not valid UTF-8, the behavior is undefined.

Unlike re2's default behavior, `.` matches line breaks. To disable this, prepend the pattern with `(?-s)`.

The pattern is not anchored. To match the entire string, anchor the pattern yourself using `^` and `$`.

If you just want to search for substrings, you can use functions [`like`](#like) or [`position`](#position) instead, which work much faster than this function.

Alternative operator syntax: `haystack REGEXP pattern` or `haystack ~ pattern` (PostgreSQL-style).

**Syntax**

```sql
match(haystack, pattern)
```

**Arguments**

* `haystack` — String in which the pattern is searched. [`String`](/reference/data-types/string)
* `pattern` — Regular expression pattern. Can be a constant or come from a column. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the pattern matches, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Basic pattern matching**

```sql title=Query
SELECT match('Hello World', 'Hello.*')
```

```text title=Response
┌─match('Hello World', 'Hello.*')─┐
│                               1 │
└─────────────────────────────────┘
```

**Pattern not matching**

```sql title=Query
SELECT match('Hello World', 'goodbye.*')
```

```text title=Response
┌─match('Hello World', 'goodbye.*')─┐
│                                 0 │
└───────────────────────────────────┘
```

**Matching a substring**

```sql title=Query
SELECT match('abcde', 'b.*d'), match('abcde', '^b.*d$')
```

```text title=Response
┌─match('abcde', 'b.*d')─┬─match('abcde', '^b.*d$')─┐
│                      1 │                        0 │
└────────────────────────┴──────────────────────────┘
```

*Introduced in version 1.1.0.*

matchCaseInsensitive [#matchcaseinsensitive]

Similar to [`match`](#match) but matches case-insensitively.

This function uses the RE2 regular expression library. Please refer to [re2](https://github.com/google/re2/wiki/Syntax) for supported syntax.

Alternative operator syntax: `haystack ~* pattern` (PostgreSQL-style).

**Syntax**

```sql
matchCaseInsensitive(haystack, pattern)
-- haystack ~* pattern
```

**Arguments**

* `haystack` — String in which the pattern is searched. [`String`](/reference/data-types/string)
* `pattern` — Regular expression pattern. Can be a constant or come from a column. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the pattern matches case-insensitively, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT matchCaseInsensitive('Hello World', 'hello.*'), 'Hello World' ~* 'HELLO.*'
```

```text title=Response
┌─matchCaseIns⋯ 'hello.*')─┬─'Hello World' ~* 'HELLO.*'─┐
│                        1 │                          1 │
└──────────────────────────┴────────────────────────────┘
```

*Introduced in version 26.8.0.*

multiFuzzyMatchAllIndices [#multifuzzymatchallindices]

Like [`multiFuzzyMatchAny`](#multiFuzzyMatchAny) but returns the array of all indices in any order that match the haystack within a constant [edit distance](https://en.wikipedia.org/wiki/Edit_distance).

**Syntax**

```sql
multiFuzzyMatchAllIndices(haystack, distance, [pattern1, pattern2, ..., patternN])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `distance` — The maximum edit distance for fuzzy matching. [`UInt8`](/reference/data-types/int-uint)
* `pattern` — Array of patterns to match against. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns an array of all indices (starting from 1) that match the haystack within the specified edit distance in any order. Returns an empty array if no matches are found. [`Array(UInt64)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query
SELECT multiFuzzyMatchAllIndices('ClickHouse', 2, ['ClickHouse', 'ClckHouse', 'ClickHose', 'House']);
```

```text title=Response
┌─multiFuzzyMa⋯, 'House'])─┐
│ [3,1,4,2]                │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiFuzzyMatchAny [#multifuzzymatchany]

Like [`multiMatchAny`](#multiMatchAny) but returns 1 if any pattern matches the haystack within a constant [edit distance](https://en.wikipedia.org/wiki/Edit_distance).
This function relies on the experimental feature of [hyperscan](https://intel.github.io/hyperscan/dev-reference/compilation.html#approximate-matching) library, and can be slow for some edge cases.
The performance depends on the edit distance value and patterns used, but it's always more expensive compared to non-fuzzy variants.

:::note
`multiFuzzyMatch*()` function family do not support UTF-8 regular expressions (it treats them as a sequence of bytes) due to restrictions of hyperscan.
:::

**Syntax**

```sql
multiFuzzyMatchAny(haystack, distance, [pattern1, pattern2, ..., patternN])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `distance` — The maximum edit distance for fuzzy matching. [`UInt8`](/reference/data-types/int-uint)
* `pattern` — Optional. An array of patterns to match against. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns `1` if any pattern matches the haystack within the specified edit distance, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiFuzzyMatchAny('ClickHouse', 2, ['ClickHouse', 'ClckHouse', 'ClickHose']);
```

```text title=Response
┌─multiFuzzyMa⋯lickHose'])─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiFuzzyMatchAnyIndex [#multifuzzymatchanyindex]

Like [`multiFuzzyMatchAny`](#multiFuzzyMatchAny) but returns any index that matches the haystack within a constant [edit distance](https://en.wikipedia.org/wiki/Edit_distance).

**Syntax**

```sql
multiFuzzyMatchAnyIndex(haystack, distance, [pattern1, pattern2, ..., patternn])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `distance` — The maximum edit distance for fuzzy matching. [`UInt8`](/reference/data-types/int-uint)
* `pattern` — Array of patterns to match against. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the index (starting from 1) of any pattern that matches the haystack within the specified edit distance, otherwise `0`. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiFuzzyMatchAnyIndex('ClickHouse', 2, ['ClckHouse', 'ClickHose', 'ClickHouse']);
```

```text title=Response
┌─multiFuzzyMa⋯ickHouse'])─┐
│                        2 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiMatchAllIndices [#multimatchallindices]

Like [`multiMatchAny`](#multiMatchAny) but returns the array of all indices that match the haystack in any order.

**Syntax**

```sql
multiMatchAllIndices(haystack, [pattern1, pattern2, ..., patternn])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `pattern` — Regular expressions to match against. [`String`](/reference/data-types/string)

**Returned value**

Array of all indices (starting from 1) that match the haystack in any order. Returns an empty array if no matches are found. [`Array(UInt64)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query
SELECT multiMatchAllIndices('ClickHouse', ['[0-9]', 'House', 'Click', 'ouse']);
```

```text title=Response
┌─multiMatchAl⋯', 'ouse'])─┐
│ [3, 2, 4]                │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiMatchAny [#multimatchany]

Check if at least one of multiple regular expression patterns matches a haystack.

If you only want to search multiple substrings in a string, you can use function [`multiSearchAny`](#multiSearchAny) instead - it works much faster than this function.

**Syntax**

```sql
multiMatchAny(haystack, pattern1[, pattern2, ...])
```

**Arguments**

* `haystack` — String in which patterns are searched. [`String`](/reference/data-types/string)
* `pattern1[, pattern2, ...]` — An array of one or more regular expression patterns. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns `1` if any pattern matches, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Multiple pattern matching**

```sql title=Query
SELECT multiMatchAny('Hello World', ['Hello.*', 'foo.*'])
```

```text title=Response
┌─multiMatchAny('Hello World', ['Hello.*', 'foo.*'])─┐
│                                                  1 │
└────────────────────────────────────────────────────┘
```

**No patterns match**

```sql title=Query
SELECT multiMatchAny('Hello World', ['goodbye.*', 'foo.*'])
```

```text title=Response
┌─multiMatchAny('Hello World', ['goodbye.*', 'foo.*'])─┐
│                                                    0 │
└──────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiMatchAnyIndex [#multimatchanyindex]

Like [`multiMatchAny`](#multiMatchAny) but returns any index that matches the haystack.

**Syntax**

```sql
multiMatchAnyIndex(haystack, [pattern1, pattern2, ..., patternn])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `pattern` — Regular expressions to match against. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the index (starting from 1) of the first pattern that matches, or 0 if no match is found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiMatchAnyIndex('ClickHouse', ['[0-9]', 'House', 'Click']);
```

```text title=Response
┌─multiMatchAn⋯, 'Click'])─┐
│                        3 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAllPositions [#multisearchallpositions]

Like [`position`](#position) but returns an array of positions (in bytes, starting at 1) for multiple `needle` substrings in a `haystack` string.

All `multiSearch*()` functions only support up to 2^8 needles.

**Syntax**

```sql
multiSearchAllPositions(haystack, needle1[, needle2, ...])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle1[, needle2, ...]` — An array of one or more substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns array of the starting position in bytes and counting from 1, if the substring was found, `0`, if the substring was not found. [`Array(UInt64)`](/reference/data-types/array)

**Examples**

**Multiple needle search**

```sql title=Query
SELECT multiSearchAllPositions('Hello, World!', ['hello', '!', 'world'])
```

```text title=Response
┌─multiSearchAllPositions('Hello, World!', ['hello', '!', 'world'])─┐
│ [0,13,0]                                                          │
└───────────────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAllPositionsCaseInsensitive [#multisearchallpositionscaseinsensitive]

Like [`multiSearchAllPositions`](#multiSearchAllPositions) but ignores case.

**Syntax**

```sql
multiSearchAllPositionsCaseInsensitive(haystack, needle1[, needle2, ...])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle1[, needle2, ...]` — An array of one or more substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns array of the starting position in bytes and counting from 1 (if the substring was found), `0` if the substring was not found. [`Array(UInt64)`](/reference/data-types/array)

**Examples**

**Case insensitive multi-search**

```sql title=Query
SELECT multiSearchAllPositionsCaseInsensitive('ClickHouse',['c','h'])
```

```text title=Response
┌─multiSearchA⋯['c', 'h'])─┐
│ [1,6]                    │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAllPositionsCaseInsensitiveUTF8 [#multisearchallpositionscaseinsensitiveutf8]

Like [`multiSearchAllPositionsUTF8`](#multiSearchAllPositionsUTF8) but ignores case.

**Syntax**

```sql
multiSearchAllPositionsCaseInsensitiveUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — UTF-8 encoded string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — UTF-8 encoded substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Array of the starting position in bytes and counting from 1 (if the substring was found). Returns 0 if the substring was not found. [`Array`](/reference/data-types/array)

**Examples**

**Case-insensitive UTF-8 search**

```sql title=Query
SELECT multiSearchAllPositionsCaseInsensitiveUTF8('Здравствуй, мир!', ['здравствуй', 'МИР']);
```

```text title=Response
┌─multiSearchA⋯й', 'МИР'])─┐
│ [1, 13]                  │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAllPositionsUTF8 [#multisearchallpositionsutf8]

Like [`multiSearchAllPositions`](#multiSearchAllPositions) but assumes `haystack` and the `needle` substrings are UTF-8 encoded strings.

**Syntax**

```sql
multiSearchAllPositionsUTF8(haystack, needle1[, needle2, ...])
```

**Arguments**

* `haystack` — UTF-8 encoded string in which the search is performed. [`String`](/reference/data-types/string)
* `needle1[, needle2, ...]` — An array of UTF-8 encoded substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns array of the starting position in bytes and counting from 1 (if the substring was found), `0` if the substring was not found. [`Array`](/reference/data-types/array)

**Examples**

**UTF-8 multi-search**

```sql title=Query
SELECT multiSearchAllPositionsUTF8('ClickHouse',['C','H'])
```

```text title=Response
┌─multiSearchAllPositionsUTF8('ClickHouse', ['C', 'H'])─┐
│ [1,6]                                                 │
└───────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAny [#multisearchany]

Checks if at least one of a number of needle strings matches the haystack string.

Functions [`multiSearchAnyCaseInsensitive`](#multiSearchAnyCaseInsensitive), [`multiSearchAnyUTF8`](#multiSearchAnyUTF8) and [`multiSearchAnyCaseInsensitiveUTF8`](#multiSearchAnyCaseInsensitiveUTF8) provide case-insensitive and/or UTF-8 variants of this function.

**Syntax**

```sql
multiSearchAny(haystack, needle1[, needle2, ...])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle1[, needle2, ...]` — An array of substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns `1`, if there was at least one match, otherwise `0`, if there was not at least one match. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Any match search**

```sql title=Query
SELECT multiSearchAny('ClickHouse',['C','H'])
```

```text title=Response
┌─multiSearchAny('ClickHouse', ['C', 'H'])─┐
│                                        1 │
└──────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAnyCaseInsensitive [#multisearchanycaseinsensitive]

Like [multiSearchAny](#multiSearchAny) but ignores case.

**Syntax**

```sql
multiSearchAnyCaseInsensitive(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns `1`, if there was at least one case-insensitive match, otherwise `0`, if there was not at least one case-insensitive match. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Case insensitive search**

```sql title=Query
SELECT multiSearchAnyCaseInsensitive('ClickHouse',['c','h'])
```

```text title=Response
┌─multiSearchAnyCaseInsensitive('ClickHouse', ['c', 'h'])─┐
│                                                       1 │
└─────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAnyCaseInsensitiveUTF8 [#multisearchanycaseinsensitiveutf8]

Like [multiSearchAnyUTF8](#multiSearchAnyUTF8) but ignores case.

**Syntax**

```sql
multiSearchAnyCaseInsensitiveUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — UTF-8 string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — UTF-8 substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns `1`, if there was at least one case-insensitive match, otherwise `0`, if there was not at least one case-insensitive match. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Given a UTF-8 string 'Здравствуйте', check if character 'з' (lowercase) is present**

```sql title=Query
SELECT multiSearchAnyCaseInsensitiveUTF8('Здравствуйте',['з'])
```

```text title=Response
┌─multiSearchA⋯те', ['з'])─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchAnyUTF8 [#multisearchanyutf8]

Like [multiSearchAny](#multiSearchAny) but assumes `haystack` and the `needle` substrings are UTF-8 encoded strings.

**Syntax**

```sql
multiSearchAnyUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — UTF-8 string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — UTF-8 substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns `1`, if there was at least one match, otherwise `0`, if there was not at least one match. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Given '你好，世界' ('Hello, world') as a UTF-8 string, check if there are any 你 or 界 characters in the string**

```sql title=Query
SELECT multiSearchAnyUTF8('你好，世界', ['你', '界']) AS result
```

```text title=Response
┌─result─┐
│      1 │
└────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstIndex [#multisearchfirstindex]

Searches for multiple needle strings in a haystack string (case-sensitive) and returns the 1-based index of the first needle found.

**Syntax**

```sql
multiSearchFirstIndex(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — The string to search in. [`String`](/reference/data-types/string)
* `needles` — Array of strings to search for. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the 1-based index (position in the needles array) of the first needle found in the haystack. Returns 0 if no needles are found. The search is case-sensitive. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiSearchFirstIndex('ClickHouse Database', ['Click', 'Database', 'Server']);
```

```text title=Response
┌─multiSearchF⋯ 'Server'])─┐
│                        1 │
└──────────────────────────┘
```

**Case-sensitive behavior**

```sql title=Query
SELECT multiSearchFirstIndex('ClickHouse Database', ['CLICK', 'Database', 'Server']);
```

```text title=Response
┌─multiSearchF⋯ 'Server'])─┐
│                        2 │
└──────────────────────────┘
```

**No match found**

```sql title=Query
SELECT multiSearchFirstIndex('Hello World', ['goodbye', 'test']);
```

```text title=Response
┌─multiSearchF⋯', 'test'])─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstIndexCaseInsensitive [#multisearchfirstindexcaseinsensitive]

Returns the index `i` (starting from 1) of the leftmost found needle\_i in the string `haystack` and 0 otherwise.
Ignores case.

**Syntax**

```sql
multiSearchFirstIndexCaseInsensitive(haystack, [needle1, needle2, ..., needleN]
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the index (starting from 1) of the leftmost found needle. Otherwise `0`, if there was no match. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiSearchFirstIndexCaseInsensitive('hElLo WoRlD', ['World', 'Hello']);
```

```text title=Response
┌─multiSearchF⋯, 'Hello'])─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstIndexCaseInsensitiveUTF8 [#multisearchfirstindexcaseinsensitiveutf8]

Searches for multiple needle strings in a haystack string, case-insensitively with UTF-8 encoding support, and returns the 1-based index of the first needle found.

**Syntax**

```sql
multiSearchFirstIndexCaseInsensitiveUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — The string to search in. [`String`](/reference/data-types/string)
* `needles` — Array of strings to search for. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the 1-based index (position in the needles array) of the first needle found in the haystack. Returns 0 if no needles are found. The search is case-insensitive and respects UTF-8 character encoding. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiSearchFirstIndexCaseInsensitiveUTF8('ClickHouse Database', ['CLICK', 'data', 'server']);
```

```text title=Response
┌─multiSearchF⋯ 'server'])─┐
│                        1 │
└──────────────────────────┘
```

**UTF-8 case handling**

```sql title=Query
SELECT multiSearchFirstIndexCaseInsensitiveUTF8('Привет Мир', ['мир', 'ПРИВЕТ']);
```

```text title=Response
┌─multiSearchF⋯ 'ПРИВЕТ'])─┐
│                        1 │
└──────────────────────────┘
```

**No match found**

```sql title=Query
SELECT multiSearchFirstIndexCaseInsensitiveUTF8('Hello World', ['goodbye', 'test']);
```

```text title=Response
┌─multiSearchF⋯', 'test'])─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstIndexUTF8 [#multisearchfirstindexutf8]

Returns the index `i` (starting from 1) of the leftmost found needle\_i in the string `haystack` and 0 otherwise.
Assumes `haystack` and `needle` are UTF-8 encoded strings.

**Syntax**

```sql
multiSearchFirstIndexUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — UTF-8 string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Array of UTF-8 substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the index (starting from 1) of the leftmost found needle. Otherwise 0, if there was no match. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT multiSearchFirstIndexUTF8('Здравствуйте мир', ['мир', 'здравствуйте']);
```

```text title=Response
┌─multiSearchF⋯вствуйте'])─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstPosition [#multisearchfirstposition]

Like [`position`](#position) but returns the leftmost offset in a `haystack` string which matches any of multiple `needle` strings.

Functions [`multiSearchFirstPositionCaseInsensitive`](#multiSearchFirstPositionCaseInsensitive), [`multiSearchFirstPositionUTF8`](#multiSearchFirstPositionUTF8) and [`multiSearchFirstPositionCaseInsensitiveUTF8`](#multiSearchFirstPositionCaseInsensitiveUTF8) provide case-insensitive and/or UTF-8 variants of this function.

**Syntax**

```sql
multiSearchFirstPosition(haystack, needle1[, needle2, ...])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle1[, needle2, ...]` — An array of one or more substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the leftmost offset in a `haystack` string which matches any of multiple `needle` strings, otherwise `0`, if there was no match. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**First position search**

```sql title=Query
SELECT multiSearchFirstPosition('Hello World',['llo', 'Wor', 'ld'])
```

```text title=Response
┌─multiSearchFirstPosition('Hello World', ['llo', 'Wor', 'ld'])─┐
│                                                             3 │
└───────────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstPositionCaseInsensitive [#multisearchfirstpositioncaseinsensitive]

Like [multiSearchFirstPosition](#multiSearchFirstPosition) but ignores case.

**Syntax**

```sql
multiSearchFirstPositionCaseInsensitive(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Array of substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the leftmost offset in a `haystack` string which matches any of multiple `needle` strings. Returns `0`, if there was no match. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Case insensitive first position**

```sql title=Query
SELECT multiSearchFirstPositionCaseInsensitive('HELLO WORLD',['wor', 'ld', 'ello'])
```

```text title=Response
┌─multiSearchFirstPositionCaseInsensitive('HELLO WORLD', ['wor', 'ld', 'ello'])─┐
│                                                                             2 │
└───────────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstPositionCaseInsensitiveUTF8 [#multisearchfirstpositioncaseinsensitiveutf8]

Like [multiSearchFirstPosition](#multiSearchFirstPosition) but assumes `haystack` and `needle` to be UTF-8 strings and ignores case.

**Syntax**

```sql
multiSearchFirstPositionCaseInsensitiveUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — UTF-8 string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Array of UTF-8 substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Returns the leftmost offset in a `haystack` string which matches any of multiple `needle` strings, ignoring case. Returns `0`, if there was no match. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Find the leftmost offset in UTF-8 string 'Здравствуй, мир' ('Hello, world') which matches any of the given needles**

```sql title=Query
SELECT multiSearchFirstPositionCaseInsensitiveUTF8('Здравствуй, мир', ['МИР', 'вст', 'Здра'])
```

```text title=Response
┌─multiSearchFirstPositionCaseInsensitiveUTF8('Здравствуй, мир', ['МИР', 'вст', 'Здра'])─┐
│                                                                                      1 │
└────────────────────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

multiSearchFirstPositionUTF8 [#multisearchfirstpositionutf8]

Like [multiSearchFirstPosition](#multiSearchFirstPosition) but assumes `haystack` and `needle` to be UTF-8 strings.

**Syntax**

```sql
multiSearchFirstPositionUTF8(haystack, [needle1, needle2, ..., needleN])
```

**Arguments**

* `haystack` — UTF-8 string in which the search is performed. [`String`](/reference/data-types/string)
* `needle` — Array of UTF-8 substrings to be searched. [`Array(String)`](/reference/data-types/array)

**Returned value**

Leftmost offset in a `haystack` string which matches any of multiple `needle` strings. Returns `0`, if there was no match. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Find the leftmost offset in UTF-8 string 'Здравствуй, мир' ('Hello, world') which matches any of the given needles**

```sql title=Query
SELECT multiSearchFirstPositionUTF8('Здравствуй, мир',['мир', 'вст', 'авст'])
```

```text title=Response
┌─multiSearchFirstPositionUTF8('Здравствуй, мир', ['мир', 'вст', 'авст'])─┐
│                                                                       4 │
└─────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramDistance [#ngramdistance]

Calculates the 4-gram distance between two strings.
For this, it counts the symmetric difference between two multisets of 4-grams and normalizes it by the sum of their cardinalities.
The smaller the returned value, the more similar the strings are.

For case-insensitive search or/and in UTF8 format use functions [`ngramDistanceCaseInsensitive`](#ngramDistanceCaseInsensitive), [`ngramDistanceUTF8`](#ngramDistanceUTF8), [`ngramDistanceCaseInsensitiveUTF8`](#ngramDistanceCaseInsensitiveUTF8).

**Syntax**

```sql
ngramDistance(haystack, needle)
```

**Arguments**

* `haystack` — String for comparison. [`String`](/reference/data-types/string)
* `needle` — String for comparison. [`String`](/reference/data-types/string)

**Returned value**

Returns a Float32 number between `0` and `1`. The smaller the returned value, the more similar the strings are. [`Float32`](/reference/data-types/float)

**Examples**

**Calculate 4-gram distance**

```sql title=Query
SELECT ngramDistance('ClickHouse', 'ClickHouses')
```

```text title=Response
┌─ngramDistance('ClickHouse', 'ClickHouses')─┐
│                                        0.1 │
└────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramDistanceCaseInsensitive [#ngramdistancecaseinsensitive]

Provides a case-insensitive variant of [`ngramDistance`](#ngramDistance).
Calculates the 4-gram distance between two strings, ignoring case.
The smaller the returned value, the more similar the strings are.

**Syntax**

```sql
ngramDistanceCaseInsensitive(haystack, needle)
```

**Arguments**

* `haystack` — First comparison string. [`String`](/reference/data-types/string)
* `needle` — Second comparison string. [`String`](/reference/data-types/string)

**Returned value**

Returns a Float32 number between `0` and `1`. [`Float32`](/reference/data-types/float)

**Examples**

**Case-insensitive 4-gram distance**

```sql title=Query
SELECT ngramDistanceCaseInsensitive('ClickHouse','clickhouse')
```

```text title=Response
┌─ngramDistanceCaseInsensitive('ClickHouse','clickhouse')─┐
│                                                       0 │
└─────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramDistanceCaseInsensitiveUTF8 [#ngramdistancecaseinsensitiveutf8]

Provides a case-insensitive UTF-8 variant of [`ngramDistance`](#ngramDistance).
Assumes that `needle` and `haystack` strings are UTF-8 encoded strings and ignores case.
Calculates the 3-gram distance between two UTF-8 strings, ignoring case.
The smaller the returned value, the more similar the strings are.

**Syntax**

```sql
ngramDistanceCaseInsensitiveUTF8(haystack, needle)
```

**Arguments**

* `haystack` — First UTF-8 encoded comparison string. [`String`](/reference/data-types/string)
* `needle` — Second UTF-8 encoded comparison string. [`String`](/reference/data-types/string)

**Returned value**

Returns a Float32 number between `0` and `1`. [`Float32`](/reference/data-types/float)

**Examples**

**Case-insensitive UTF-8 3-gram distance**

```sql title=Query
SELECT ngramDistanceCaseInsensitiveUTF8('abcde','CDE')
```

```text title=Response
┌─ngramDistanceCaseInsensitiveUTF8('abcde','CDE')─┐
│                                             0.5 │
└─────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramDistanceUTF8 [#ngramdistanceutf8]

Provides a UTF-8 variant of [`ngramDistance`](#ngramDistance).
Assumes that `needle` and `haystack` strings are UTF-8 encoded strings.
Calculates the 3-gram distance between two UTF-8 strings.
The smaller the returned value, the more similar the strings are.

**Syntax**

```sql
ngramDistanceUTF8(haystack, needle)
```

**Arguments**

* `haystack` — First UTF-8 encoded comparison string. [`String`](/reference/data-types/string)
* `needle` — Second UTF-8 encoded comparison string. [`String`](/reference/data-types/string)

**Returned value**

Returns a Float32 number between `0` and `1`. [`Float32`](/reference/data-types/float)

**Examples**

**UTF-8 3-gram distance**

```sql title=Query
SELECT ngramDistanceUTF8('abcde','cde')
```

```text title=Response
┌─ngramDistanceUTF8('abcde', 'cde')─┐
│                               0.5 │
└───────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramSearch [#ngramsearch]

Checks if the 4-gram distance between two strings is less than or equal to a given threshold.

For case-insensitive search or/and in UTF8 format use functions `ngramSearchCaseInsensitive`, `ngramSearchUTF8`, `ngramSearchCaseInsensitiveUTF8`.

**Syntax**

```sql
ngramSearch(haystack, needle)
```

**Arguments**

* `haystack` — String for comparison. [`String`](/reference/data-types/string)
* `needle` — String for comparison. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the 4-gram distance between the strings is less than or equal to a threshold (`1.0` by default), `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Search using 4-grams**

```sql title=Query
SELECT ngramSearch('ClickHouse', 'Click')
```

```text title=Response
┌─ngramSearch('ClickHouse', 'Click')─┐
│                                  1 │
└────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramSearchCaseInsensitive [#ngramsearchcaseinsensitive]

Provides a case-insensitive variant of [`ngramSearch`](#ngramSearch).
Calculates the non-symmetric difference between a needle string and a haystack string, i.e. the number of n-grams from the needle minus the common number of n-grams normalized by the number of needle n-grams.
Checks if the 4-gram distance between two strings is less than or equal to a given threshold, ignoring case.

**Syntax**

```sql
ngramSearchCaseInsensitive(haystack, needle)
```

**Arguments**

* `haystack` — String for comparison. [`String`](/reference/data-types/string)
* `needle` — String for comparison. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the 4-gram distance between the strings is less than or equal to a threshold (`1.0` by default), `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Case-insensitive search using 4-grams**

```sql title=Query
SELECT ngramSearchCaseInsensitive('Hello World','hello')
```

```text title=Response
┌─ngramSearchCaseInsensitive('Hello World', 'hello')─┐
│                                                  1 │
└────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramSearchCaseInsensitiveUTF8 [#ngramsearchcaseinsensitiveutf8]

Provides a case-insensitive UTF-8 variant of [`ngramSearch`](#ngramSearch).
Assumes `haystack` and `needle` to be UTF-8 strings and ignores case.
Checks if the 3-gram distance between two UTF-8 strings is less than or equal to a given threshold, ignoring case.

**Syntax**

```sql
ngramSearchCaseInsensitiveUTF8(haystack, needle)
```

**Arguments**

* `haystack` — UTF-8 string for comparison. [`String`](/reference/data-types/string)
* `needle` — UTF-8 string for comparison. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the 3-gram distance between the strings is less than or equal to a threshold (`1.0` by default), `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Case-insensitive UTF-8 search using 3-grams**

```sql title=Query
SELECT ngramSearchCaseInsensitiveUTF8('абвГДЕёжз', 'АбвгдЕЁжз')
```

```text title=Response
┌─ngramSearchCaseInsensitiveUTF8('абвГДЕёжз', 'АбвгдЕЁжз')─┐
│                                                        1 │
└──────────────────────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

ngramSearchUTF8 [#ngramsearchutf8]

Provides a UTF-8 variant of `ngramSearch`.
Assumes `haystack` and `needle` to be UTF-8 strings.
Checks if the 3-gram distance between two UTF-8 strings is less than or equal to a given threshold.

**Syntax**

```sql
ngramSearchUTF8(haystack, needle)
```

**Arguments**

* `haystack` — UTF-8 string for comparison. [`String`](/reference/data-types/string)
* `needle` — UTF-8 string for comparison. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the 3-gram distance between the strings is less than or equal to a threshold (`1.0` by default), `0` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**UTF-8 search using 3-grams**

```sql title=Query
SELECT ngramSearchUTF8('абвгдеёжз', 'гдеёзд')
```

```text title=Response
┌─ngramSearchUTF8('абвгдеёжз', 'гдеёзд')─┐
│                                      1 │
└────────────────────────────────────────┘
```

*Introduced in version 20.1.0.*

notILike [#notilike]

Checks whether a string does not match a pattern, case-insensitive. The pattern can contain special characters `%` and `_` for SQL LIKE matching. Supports the optional `ESCAPE` clause (see `like`).

**Syntax**

```sql
notILike(haystack, pattern[, escape_character])
-- haystack NOT ILIKE pattern [ESCAPE 'escape_character']
```

**Arguments**

* `haystack` — The input string to search in. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `pattern` — The SQL LIKE pattern to match against. `%` matches any number of characters (including zero), `_` matches exactly one character. [`String`](/reference/data-types/string)
* `escape_character` — Optional single-character string to use as the escape character instead of `\`. Default: `\`. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the string does not match the pattern (case-insensitive), otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT notILike('ClickHouse', '%house%');
```

```text title=Response
┌─notILike('Cl⋯ '%house%')─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 20.6.0.*

notLike [#notlike]

Similar to [`like`](#like) but negates the result. Supports the optional `ESCAPE` clause (see `like`).

**Syntax**

```sql
notLike(haystack, pattern[, escape_character])
-- haystack NOT LIKE pattern [ESCAPE 'escape_character']
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
* `pattern` — LIKE pattern to match against. [`String`](/reference/data-types/string)
* `escape_character` — Optional single-character string to use as the escape character instead of `\`. Default: `\`. [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the string does not match the `LIKE` pattern, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT notLike('ClickHouse', '%House%');
```

```text title=Response
┌─notLike('Cli⋯ '%House%')─┐
│                        0 │
└──────────────────────────┘
```

**Non-matching pattern**

```sql title=Query
SELECT notLike('ClickHouse', '%SQL%');
```

```text title=Response
┌─notLike('Cli⋯', '%SQL%')─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

notMatch [#notmatch]

Similar to [`match`](#match) but negates the result: checks that the string does not match the regular expression pattern.

This function uses the RE2 regular expression library. Please refer to [re2](https://github.com/google/re2/wiki/Syntax) for supported syntax.

Alternative operator syntax: `haystack !~ pattern` (PostgreSQL-style).

**Syntax**

```sql
notMatch(haystack, pattern)
-- haystack !~ pattern
```

**Arguments**

* `haystack` — String in which the pattern is searched. [`String`](/reference/data-types/string)
* `pattern` — Regular expression pattern. Can be a constant or come from a column. [`String`](/reference/data-types/string)

**Returned value**

Returns `0` if the pattern matches, `1` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT notMatch('Hello World', 'Hello.*'), 'Hello World' !~ 'goodbye.*'
```

```text title=Response
┌─notMatch('He⋯ 'Hello.*')─┬─'Hello World⋯goodbye.*'─┐
│                        0 │                       1 │
└──────────────────────────┴─────────────────────────┘
```

*Introduced in version 26.8.0.*

notMatchCaseInsensitive [#notmatchcaseinsensitive]

Similar to [`matchCaseInsensitive`](#matchCaseInsensitive) but negates the result: checks that the string does not match the regular expression pattern case-insensitively.

This function uses the RE2 regular expression library. Please refer to [re2](https://github.com/google/re2/wiki/Syntax) for supported syntax.

Alternative operator syntax: `haystack !~* pattern` (PostgreSQL-style).

**Syntax**

```sql
notMatchCaseInsensitive(haystack, pattern)
-- haystack !~* pattern
```

**Arguments**

* `haystack` — String in which the pattern is searched. [`String`](/reference/data-types/string)
* `pattern` — Regular expression pattern. Can be a constant or come from a column. [`String`](/reference/data-types/string)

**Returned value**

Returns `0` if the pattern matches case-insensitively, `1` otherwise. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query
SELECT notMatchCaseInsensitive('Hello World', 'hello.*'), 'Hello World' !~* 'GOODBYE.*'
```

```text title=Response
┌─notMatchCase⋯ 'hello.*')─┬─'Hello World⋯GOODBYE.*'─┐
│                        0 │                       1 │
└──────────────────────────┴─────────────────────────┘
```

*Introduced in version 26.8.0.*

position [#position]

Returns the position (in bytes, starting at 1) of a substring `needle` in a string `haystack`.

If substring `needle` is empty, these rules apply:

* if no `start_pos` was specified: return `1`
* if `start_pos = 0`: return `1`
* if `start_pos >= 1` and `start_pos <= length(haystack) + 1`: return `start_pos`
* otherwise: return `0`

The same rules also apply to functions [`locate`](#locate), [`positionCaseInsensitive`](#positionCaseInsensitive), [`positionUTF8`](#positionUTF8) and [`positionCaseInsensitiveUTF8`](#positionCaseInsensitiveUTF8).

**Syntax**

```sql
position(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `start_pos` — Position (1-based) in `haystack` at which the search starts. Optional. [`UInt`](/reference/data-types/int-uint)

**Returned value**

Returns starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query
SELECT position('Hello, world!', '!')
```

```text title=Response
┌─position('Hello, world!', '!')─┐
│                             13 │
└────────────────────────────────┘
```

**With start\_pos argument**

```sql title=Query
SELECT position('Hello, world!', 'o', 1), position('Hello, world!', 'o', 7)
```

```text title=Response
┌─position('Hello, world!', 'o', 1)─┬─position('Hello, world!', 'o', 7)─┐
│                                 5 │                                 9 │
└───────────────────────────────────┴───────────────────────────────────┘
```

**Needle IN haystack syntax**

```sql title=Query
SELECT 6 = position('/' IN s) FROM (SELECT 'Hello/World' AS s)
```

```text title=Response
┌─equals(6, position(s, '/'))─┐
│                           1 │
└─────────────────────────────┘
```

**Empty needle substring**

```sql title=Query
SELECT position('abc', ''), position('abc', '', 0), position('abc', '', 1), position('abc', '', 2), position('abc', '', 3), position('abc', '', 4), position('abc', '', 5)
```

```text title=Response
┌─position('abc', '')─┬─position('abc', '', 0)─┬─position('abc', '', 1)─┬─position('abc', '', 2)─┬─position('abc', '', 3)─┬─position('abc', '', 4)─┬─position('abc', '', 5)─┐
│                   1 │                      1 │                      1 │                      2 │                      3 │                      4 │                      0 │
└─────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┘
```

*Introduced in version 1.1.0.*

positionCaseInsensitive [#positioncaseinsensitive]

Like [`position`](#position) but case-insensitive.

**Syntax**

```sql
positionCaseInsensitive(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Case insensitive search**

```sql title=Query
SELECT positionCaseInsensitive('Hello, world!', 'hello')
```

```text title=Response
┌─positionCaseInsensitive('Hello, world!', 'hello')─┐
│                                                 1 │
└───────────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

positionCaseInsensitiveUTF8 [#positioncaseinsensitiveutf8]

Like [`positionUTF8`](#positionUTF8) but searches case-insensitively.

**Syntax**

```sql
positionCaseInsensitiveUTF8(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Case insensitive UTF-8 search**

```sql title=Query
SELECT positionCaseInsensitiveUTF8('Привет мир', 'МИР')
```

```text title=Response
┌─positionCaseInsensitiveUTF8('Привет мир', 'МИР')─┐
│                                                8 │
└──────────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

positionUTF8 [#positionutf8]

Like [`position`](#position) but assumes `haystack` and `needle` are UTF-8 encoded strings.

**Syntax**

```sql
positionUTF8(haystack, needle[, start_pos])
```

**Arguments**

* `haystack` — String in which the search is performed. [`String`](/reference/data-types/string) or [`Enum`](/reference/data-types/enum)
* `needle` — Substring to be searched. [`String`](/reference/data-types/string)
* `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**UTF-8 character counting**

```sql title=Query
SELECT positionUTF8('Motörhead', 'r')
```

```text title=Response
┌─position('Motörhead', 'r')─┐
│                          5 │
└────────────────────────────┘
```

*Introduced in version 1.1.0.*
