Functions for Searching in Strings

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

**Syntax**

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

**Aliases**: `REGEXP_MATCHES`

**Arguments**

- `haystack` — String in which the pattern is searched. [`String`](/docs/sql-reference/data-types/string)
- `pattern` — Regular expression pattern. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Basic pattern matching**

```
SELECT match('Hello World', 'Hello.*')
```

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

**Pattern not matching**

```
SELECT match('Hello World', 'goodbye.*')
```

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

**Matching a substring**

```
SELECT match('abcde', 'b.*d'), match('abcde', '^b.*d$')
```

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

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

Introduced in: v20.1.0

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

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

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `distance` — The maximum edit distance for fuzzy matching. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `pattern` — Array of patterns to match against. [`Array(String)`](/docs/sql-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)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT multiFuzzyMatchAllIndices('ClickHouse', 2, ['ClickHouse', 'ClckHouse', 'ClickHose', 'House']);
```

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

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

Introduced in: v20.1.0

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

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

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```
SELECT multiFuzzyMatchAny('ClickHouse', 2, ['ClickHouse', 'ClckHouse', 'ClickHose']);
```

```
┌─multiFuzzyMa⋯lickHose'])─┐
│                        1 │
└──────────────────────────┘
```

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

Introduced in: v20.1.0

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

**Syntax**

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

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string)
- `distance` — The maximum edit distance for fuzzy matching. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `pattern` — Array of patterns to match against. [`Array(String)`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT multiFuzzyMatchAnyIndex('ClickHouse', 2, ['ClckHouse', 'ClickHose', 'ClickHouse']);
```

```
┌─multiFuzzyMa⋯ickHouse'])─┐
│                        2 │
└──────────────────────────┘
```

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

Introduced in: v20.1.0

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

**Syntax**

```
multiMatchAllIndices(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. [`String`](/docs/sql-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)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

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

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

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

Introduced in: v20.1.0

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

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

**Arguments**

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

**Returned value**

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

**Examples**

**Multiple pattern matching**

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