Functions for Searching in Strings

**Examples**

**Usage example**

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

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

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

Introduced in: v20.1.0

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

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

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `needle1[, needle2, ...]` — An array of one or more substrings to be searched. [`Array(String)`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**First position search**

```
SELECT multiSearchFirstPosition('Hello World',['llo', 'Wor', 'ld'])
```

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

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `needle` — Array of substrings to be searched. [`Array(String)`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Case insensitive first position**

```
SELECT multiSearchFirstPositionCaseInsensitive('HELLO WORLD',['wor', 'ld', 'ello'])
```

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

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

- `haystack` — UTF-8 string in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `needle` — Array of UTF-8 substrings to be searched. [`Array(String)`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

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

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

```
┌─multiSearchFirstPositionCaseInsensitiveUTF8('Здравствуй, мир', ['мир', 'вст', 'Здра'])─┐
│                                                                                      3 │
└────────────────────────────────────────────────────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

- `haystack` — UTF-8 string in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `needle` — Array of UTF-8 substrings to be searched. [`Array(String)`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

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

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

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

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

Introduced in: v20.1.0

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

```
ngramDistance(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 a Float32 number between `0` and `1`. The smaller the returned value, the more similar the strings are. [`Float32`](/docs/sql-reference/data-types/float)

**Examples**

**Calculate 4-gram distance**

```
SELECT ngramDistance('ClickHouse', 'ClickHouses')
```

```
┌─ngramDistance('ClickHouse', 'ClickHouses')─┐
│                                        0.1 │
└────────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

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

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

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