Functions for Searching in Strings

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)

**Examples**

**Extract domain from email**

```
SELECT extract('[email protected]', '.*@(.*)$')
```

```
┌─extract('[email protected]', '.*@(.*)$')─┐
│ clickhouse.com                            │
└───────────────────────────────────────────┘
```

**No match returns empty string**

```
SELECT extract('[email protected]', 'no_match')
```

```
┌─extract('[email protected]', 'no_match')─┐
│                                            │
└────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Like [`extract`](#extract), but returns an array of all matches of a regular expression in a string.
If 'haystack' doesn't match the 'pattern' regex, an empty array is returned.

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

**Syntax**

```
extractAll(haystack, pattern)
```

**Arguments**

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

**Returned value**

Returns array of extracted fragments. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Extract all numbers**

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

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

**Extract using capturing group**

```
SELECT extractAll('[email protected], [email protected]', '([a-zA-Z0-9]+)@')
```

```
┌─extractAll('[email protected], [email protected]', '([a-zA-Z0-9]+)@')─┐
│ ['test','user']                                                    │
└────────────────────────────────────────────────────────────────────┘
```

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

Introduced in: v20.5.0

Matches all groups of a string using the provided regular expression and returns an array of arrays, where each array contains all captures from the same capturing group, organized by group number.

**Syntax**

```
extractAllGroupsHorizontal(s, regexp)
```

**Arguments**

- `s` — Input string to extract from. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `regexp` — Regular expression to match by. [`const String`](/docs/sql-reference/data-types/string) or [`const FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns an array of arrays, where each inner array contains all captures from one capturing group across all matches. The first inner array contains all captures from group 1, the second from group 2, etc. If no matches are found, returns an empty array. [`Array(Array(String))`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
WITH '< Server: nginx
< Date: Tue, 22 Jan 2019 00:26:14 GMT
< Content-Type: text/html; charset=UTF-8
< Connection: keep-alive
' AS s
SELECT extractAllGroupsHorizontal(s, '< ([\\w\\-]+): ([^\\r\\n]+)');
```

```
[['Server','Date','Content-Type','Connection'],['nginx','Tue, 22 Jan 2019 00:26:14 GMT','text/html; charset=UTF-8','keep-alive']]
```

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

Introduced in: v20.5.0

Extracts all groups from non-overlapping substrings matched by a regular expression.

**Syntax**

```
extractGroups(s, regexp)
```

**Arguments**

- `s` — Input string to extract from. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `regexp` — Regular expression. Constant. [`const String`](/docs/sql-reference/data-types/string) or [`const FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

If the function finds at least one matching group, it returns Array(Array(String)) column, clustered by group\_id (`1` to `N`, where `N` is number of capturing groups in regexp). If there is no matching group, it returns an empty array. [`Array(Array(String))`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
WITH '< Server: nginx
< Date: Tue, 22 Jan 2019 00:26:14 GMT
< Content-Type: text/html; charset=UTF-8
< Connection: keep-alive
' AS s
SELECT extractGroups(s, '< ([\\w\\-]+): ([^\\r\\n]+)');
```

```
[['Server','nginx'],['Date','Tue, 22 Jan 2019 00:26:14 GMT'],['Content-Type','text/html; charset=UTF-8'],['Connection','keep-alive']]
```

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

Introduced in: v25.10.0

Like [`hasAnyTokens`](#hasAnyTokens), but returns 1, if all tokens in the `needle` string or array match the `input` string, and 0 otherwise. If `input` is a column, returns all rows that satisfy this condition.

Note

Column `input` should have a [text index](/docs/engines/table-engines/mergetree-family/textindexes) defined for optimal performance.
If no text index is defined, the function performs a brute-force column scan which is orders of magnitude slower than an index lookup.

Prior to searching, the function tokenizes

- the `input` argument (always), and
- the `needle` argument (if given as a [String](/docs/sql-reference/data-types/string))
  using the tokenizer specified for the text index.
  If the column has no text index defined, the `splitByNonAlpha` tokenizer is used instead.
  If the `needle` argument is of type [Array(String)](/docs/sql-reference/data-types/array), each array element is treated as a token — no additional tokenization takes place.

Duplicate tokens are ignored.
For example, needles = ['ClickHouse', 'ClickHouse'] is treated the same as ['ClickHouse'].

**Syntax**

```
hasAllTokens(input, needles)
```

**Aliases**: `hasAllToken`

**Arguments**

- `input` — The input column. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) or [`Array(String)`](/docs/sql-reference/data-types/array) or [`Array(FixedString)`](/docs/sql-reference/data-types/array)
- `needles` — Tokens to be searched. [`String`](/docs/sql-reference/data-types/string) or [`Array(String)`](/docs/sql-reference/data-types/array)
- `tokenizer` — The tokenizer to use. Valid arguments are `splitByNonAlpha`, `splitByString`, `asciiCJK`, `ngrams`, `sparseGrams`, and `array`. Optional, if not set explicitly, defaults to `splitByNonAlpha`. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns 1, if all needles match. 0, otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage with a string needle**

```
CREATE TABLE table (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\']))
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO table VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM table WHERE hasAllTokens(msg, 'a\\d()');
```

```
┌─count()─┐
│       1 │
└─────────┘
```

**Specify needles to be searched for AS-IS (no tokenization) in an array**

```
SELECT count() FROM table WHERE hasAllTokens(msg, ['a', 'd']);
```

```
┌─count()─┐
│       1 │
└─────────┘
```

**Generate needles using the `tokens` function**