Functions for Searching in Strings

**Returned value**

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

**Examples**

**Basic usage with a string needle**

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

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

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

```
┌─count()─┐
│       1 │
└─────────┘
```

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

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

```
┌─count()─┐
│       1 │
└─────────┘
```

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

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

```
┌─count()─┐
│       1 │
└─────────┘
```

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

```
SELECT hasAllTokens('abcdef', 'abc', 'ngrams(3)');
```

```
┌─hasAllTokens('abcdef', 'abc', 'ngrams(3)')─┐
│                                            1 │
└──────────────────────────────────────────────┘
```

**Usage examples for array and map columns**

```
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**

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

```
┌─count()─┐
│       1 │
└─────────┘
```

**Example with mapKeys**

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

```
┌─count()─┐
│       1 │
└─────────┘
```

**Example with mapValues**

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

```
┌─count()─┐
│       0 │
└─────────┘
```

## hasAnyTokens[​](#hasAnyTokens "Direct link to hasAnyTokens")

Introduced in: v25.10.0

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](/docs/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](/docs/sql-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)](/docs/sql-reference/data-types/array), each array element is treated as a token — no additional tokenization takes place.

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

**Syntax**

```
hasAnyTokens(input, needles)
```

**Aliases**: `hasAnyToken`

**Arguments**

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

**Returned value**

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

**Examples**

**Basic usage with a string needle**

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

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

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

```
┌─count()─┐
│       3 │
└─────────┘
```

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

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

```
┌─count()─┐
│       3 │
└─────────┘
```

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

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

```
┌─count()─┐
│       3 │
└─────────┘
```

**Usage examples for array and map columns**

```
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**

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

```
┌─count()─┐
│       1 │
└─────────┘
```

**Example with mapKeys**

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

```
┌─count()─┐
│       2 │
└─────────┘
```

**Example with mapValues**

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

```
┌─count()─┐
│       2 │
└─────────┘
```

## hasPhrase[​](#hasPhrase "Direct link to hasPhrase")

Introduced in: v26.4.0

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

Prior to searching, the function tokenizes both the `input` and the `phrase` arguments using the tokenizer specified as the optional third argument.
If no tokenizer is specified, by default the `splitByNonAlpha` tokenizer would be used.

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**

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

**Aliases**: `matchPhrase`

**Arguments**

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

**Returned value**

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

**Examples**

**Phrase match**

```
SELECT hasPhrase('the quick brown fox jumps', 'quick brown')
```

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

**Non-consecutive tokens**