Functions for Searching in Strings

**Arguments**

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

**Returned value**

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

**Examples**

**Case-insensitive 4-gram distance**

```
SELECT ngramDistanceCaseInsensitive('ClickHouse','clickhouse')
```

```
┌─ngramDistanceCaseInsensitive('ClickHouse','clickhouse')─┐
│                                                       0 │
└─────────────────────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

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

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

**Arguments**

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

**Returned value**

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

**Examples**

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

```
SELECT ngramDistanceCaseInsensitiveUTF8('abcde','CDE')
```

```
┌─ngramDistanceCaseInsensitiveUTF8('abcde','CDE')─┐
│                                             0.5 │
└─────────────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

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

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

**Arguments**

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

**Returned value**

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

**Examples**

**UTF-8 3-gram distance**

```
SELECT ngramDistanceUTF8('abcde','cde')
```

```
┌─ngramDistanceUTF8('abcde','cde')─┐
│                               0.5 │
└───────────────────────────────────┘
```

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

Introduced in: v20.1.0

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

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

**Arguments**

- `haystack` — String for comparison. [`String`](/docs/sql-reference/data-types/string)
- `needle` — String for comparison. [`String`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Search using 4-grams**

```
SELECT ngramSearch('ClickHouse', 'Click')
```

```
┌─ngramSearch('ClickHouse', 'Click')─┐
│                                  1 │
└────────────────────────────────────┘
```

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

Introduced in: v20.1.0

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

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

**Arguments**

- `haystack` — String for comparison. [`String`](/docs/sql-reference/data-types/string)
- `needle` — String for comparison. [`String`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

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

```
SELECT ngramSearchCaseInsensitive('Hello World','hello')
```

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

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

Introduced in: v20.1.0

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

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

**Arguments**

- `haystack` — UTF-8 string for comparison. [`String`](/docs/sql-reference/data-types/string)
- `needle` — UTF-8 string for comparison. [`String`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

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

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

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

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

Introduced in: v20.1.0

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

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

**Arguments**

- `haystack` — UTF-8 string for comparison. [`String`](/docs/sql-reference/data-types/string)
- `needle` — UTF-8 string for comparison. [`String`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

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

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

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

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

Introduced in: v20.6.0

Checks whether a string does not match a pattern, case-insensitive. The pattern can contain special characters `%` and `_` for SQL LIKE matching.

**Syntax**

```
notILike(haystack, pattern)
```

**Arguments**

- `haystack` — The input string to search in. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `pattern` — The SQL LIKE pattern to match against. `%` matches any number of characters (including zero), `_` matches exactly one character. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Usage example**

```
SELECT notILike('ClickHouse', '%house%');
```

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