Functions for Searching in Strings

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/string-search-functions.md)

All functions in this section search case-sensitively by default. Case-insensitive search is usually provided by separate function variants.

Note

Case-insensitive search follows the lowercase-uppercase rules of the English language. E.g. Uppercased `i` in the English language is
`I` whereas in the Turkish language it is `İ` - results for languages other than English may be unexpected.

Functions in this section also assume that the searched string (referred to in this section as `haystack`) and the search string (referred to in this section as `needle`) are single-byte encoded text. If this assumption is
violated, no exception is thrown and results are undefined. Search with UTF-8 encoded strings is usually provided by separate function
variants. Likewise, if a UTF-8 function variant is used and the input strings are not UTF-8 encoded text, no exception is thrown and the
results are undefined. Note that no automatic Unicode normalization is performed, however you can use the
[normalizeUTF8\*()](/docs/sql-reference/functions/string-functions#normalizeUTF8NFC) functions for that.

[General strings functions](/docs/sql-reference/functions/string-functions) and [functions for replacing in strings](/docs/sql-reference/functions/string-replace-functions) are described separately.

Note

The documentation below is generated from the `system.functions` system table.

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

Introduced in: v21.1.0

Returns number of matches of a regular expression in a string.

Version dependent behavior

The behavior of this function depends on the ClickHouse version:

- in versions < v25.6, the function stops counting at the first empty match even if a pattern accepts.
- in versions >= 25.6, the function continues execution when an empty match occurs. The legacy behavior can be restored using setting `count_matches_stop_at_empty_match = true`;

**Syntax**

```
countMatches(haystack, pattern)
```

**Arguments**

- `haystack` — The string to search in. [`String`](/docs/sql-reference/data-types/string)
- `pattern` — Regular expression pattern. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the number of matches found. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Count digit sequences**

```
SELECT countMatches('hello 123 world 456 test', '[0-9]+')
```

```
┌─countMatches('hello 123 world 456 test', '[0-9]+')─┐
│                                                   2 │
└─────────────────────────────────────────────────────┘
```

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

Introduced in: v21.1.0

Like [`countMatches`](#countMatches) but performs case-insensitive matching.

**Syntax**

```
countMatchesCaseInsensitive(haystack, pattern)
```

**Arguments**

- `haystack` — The string to search in. [`String`](/docs/sql-reference/data-types/string)
- `pattern` — Regular expression pattern. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the number of matches found. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Case insensitive count**

```
SELECT countMatchesCaseInsensitive('Hello HELLO world', 'hello')
```

```
┌─countMatchesCaseInsensitive('Hello HELLO world', 'hello')─┐
│                                                         2 │
└───────────────────────────────────────────────────────────┘
```

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

Introduced in: v21.1.0

Returns how often a substring `needle` occurs in a string `haystack`.

**Syntax**

```
countSubstrings(haystack, needle[, start_pos])
```

**Arguments**

- `haystack` — String in which the search is performed. [String](/docs/sql-reference/data-types/string) or [Enum](/docs/sql-reference/data-types/enum). - `needle` — Substring to be searched. [String](/docs/sql-reference/data-types/string). - `start_pos` — Position (1-based) in `haystack` at which the search starts. [UInt](/docs/sql-reference/data-types/int-uint). Optional.

**Returned value**

The number of occurrences. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT countSubstrings('aaaa', 'aa');
```

```
┌─countSubstrings('aaaa', 'aa')─┐
│                             2 │
└───────────────────────────────┘
```

**With start\_pos argument**

```
SELECT countSubstrings('abc___abc', 'abc', 4);
```

```
┌─countSubstrings('abc___abc', 'abc', 4)─┐
│                                      1 │
└────────────────────────────────────────┘
```

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

Introduced in: v21.1.0

Like [`countSubstrings`](#countSubstrings) but counts case-insensitively.

**Syntax**

```
countSubstringsCaseInsensitive(haystack, needle[, start_pos])
```

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string) or [`Enum`](/docs/sql-reference/data-types/enum)
- `needle` — Substring to be searched. [`String`](/docs/sql-reference/data-types/string)
- `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the number of occurrences of the neddle in the haystack. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT countSubstringsCaseInsensitive('AAAA', 'aa');
```

```
┌─countSubstri⋯AAA', 'aa')─┐
│                        2 │
└──────────────────────────┘
```

**With start\_pos argument**

```
SELECT countSubstringsCaseInsensitive('abc___ABC___abc', 'abc', 4);
```

```
┌─countSubstri⋯, 'abc', 4)─┐
│                        2 │
└──────────────────────────┘
```

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

Introduced in: v21.1.0

Like [`countSubstrings`](#countSubstrings) but counts case-insensitively and assumes that haystack is a UTF-8 string.

**Syntax**

```
countSubstringsCaseInsensitiveUTF8(haystack, needle[, start_pos])
```

**Arguments**

- `haystack` — UTF-8 string in which the search is performed. [`String`](/docs/sql-reference/data-types/string) or [`Enum`](/docs/sql-reference/data-types/enum)
- `needle` — Substring to be searched. [`String`](/docs/sql-reference/data-types/string)
- `start_pos` — Optional. Position (1-based) in `haystack` at which the search starts. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the number of occurrences of the needle in the haystack. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА');
```

```
┌─countSubstri⋯шка', 'КА')─┐
│                        4 │
└──────────────────────────┘
```

**With start\_pos argument**

```
SELECT countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА', 13);
```

```
┌─countSubstri⋯, 'КА', 13)─┐
│                        2 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Extracts the first match of a regular expression in a string.
If 'haystack' doesn't match 'pattern', an empty string is returned.

This function uses the RE2 regular expression library. Please refer to [re2](https://github.com/google/re2/wiki/Syntax) for supported syntax.

If the regular expression has capturing groups (sub-patterns), the function matches the input string against the first capturing group.

**Syntax**

```
extract(haystack, pattern)
```

**Arguments**

- `haystack` — String from which to extract. [`String`](/docs/sql-reference/data-types/string)
- `pattern` — Regular expression, typically containing a capturing group. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns extracted fragment as a string. [`String`](/docs/sql-reference/data-types/string)