Functions for Searching in Strings

```
SELECT multiMatchAny('Hello World', ['Hello.*', 'foo.*'])
```

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

**No patterns match**

```
SELECT multiMatchAny('Hello World', ['goodbye.*', 'foo.*'])
```

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

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `pattern` — Regular expressions to match against. [`Array(String)`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

```
┌─multiMatchAn⋯, 'Click'])─┐
│                        3 │
└──────────────────────────┘
```

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

Introduced in: v20.1.0

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

```
multiSearchAllPositions(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 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)`](/docs/sql-reference/data-types/array)

**Examples**

**Multiple needle search**

```
SELECT multiSearchAllPositions('Hello, World!', ['hello', '!', 'world'])
```

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

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

Introduced in: v20.1.0

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

**Syntax**

```
multiSearchAllPositionsCaseInsensitive(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 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)`](/docs/sql-reference/data-types/array)

**Examples**

**Case insensitive multi-search**

```
SELECT multiSearchAllPositionsCaseInsensitive('ClickHouse',['c','h'])
```

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

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

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

**Examples**

**Case-insensitive UTF-8 search**

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

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

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

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

**Examples**

**UTF-8 multi-search**

```
SELECT multiSearchAllPositionsUTF8('ClickHouse',['C','H'])
```

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

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

Introduced in: v20.1.0

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

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

**Arguments**

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

**Examples**

**Any match search**

```
SELECT multiSearchAny('ClickHouse',['C','H'])
```

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

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

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

**Examples**

**Case insensitive search**

```
SELECT multiSearchAnyCaseInsensitive('ClickHouse',['c','h'])
```

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

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

Introduced in: v20.1.0