Functions for Searching in Strings

**Returned value**

Returns `1` if the token is found, `0` otherwise, null if token is ill-formed. [`Nullable(UInt8)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Usage example**

```
SELECT hasTokenOrNull('apple banana cherry', 'ban ana');
```

```
┌─hasTokenOrNu⋯ 'ban ana')─┐
│                     ᴺᵁᴸᴸ │
└──────────────────────────┘
```

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

Introduced in: v26.4.0

Highlights occurrences of search terms in a text string by wrapping them with HTML tags.

The function performs ASCII case-insensitive matching. If multiple search terms overlap or are adjacent in the text, the matched regions are merged into a single highlighted span.

**Syntax**

```
highlight(haystack, needles[, open_tag, close_tag])
```

**Arguments**

- `haystack` — The text to search in. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `needles` — An array of search terms to highlight. [`const Array(String)`](/docs/sql-reference/data-types/array)
- `open_tag` — The opening tag to insert before each match. Default: `<em>`. [`const String`](/docs/sql-reference/data-types/string)
- `close_tag` — The closing tag to insert after each match. Default: `</em>`. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the input text with matched terms wrapped in the specified tags. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic highlight**

```
SELECT highlight('The quick brown fox', ['quick', 'fox'])
```

```
┌─highlight('The quick brown fox', ['quick', 'fox'])─┐
│ The <em>quick</em> brown <em>fox</em>              │
└────────────────────────────────────────────────────┘
```

**Custom tags**

```
SELECT highlight('Hello World', ['hello'], '<b>', '</b>')
```

```
┌─highlight('Hello World', ['hello'], '<b>', '</b>')─┐
│ <b>Hello</b> World                                 │
└────────────────────────────────────────────────────┘
```

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

Introduced in: v20.6.0

Like [`like`](#like) but searches case-insensitively.

**Syntax**

```
ilike(haystack, pattern)
-- haystack ILIKE pattern
```

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `pattern` — LIKE pattern to match against. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Usage example**

```
SELECT ilike('ClickHouse', '%house%');
```

```
┌─ilike('ClickHouse', '%house%')─┐
│                              1 │
└────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns whether string `haystack` matches the `LIKE` expression `pattern`.

A `LIKE` expression can contain normal characters and the following metasymbols:

- `%` indicates an arbitrary number of arbitrary characters (including zero characters).
- `_` indicates a single arbitrary character.
- `\` is for escaping literals `%`, `_` and `\`.

Matching is based on UTF-8, e.g. `_` matches the Unicode code point `¥` which is represented in UTF-8 using two bytes.

If the haystack or the `LIKE` expression are not valid UTF-8, the behavior is undefined.

No automatic Unicode normalization is performed, you can use the `normalizeUTF8*` functions for that.

To match against literal `%`, `_` and `\` (which are `LIKE` metacharacters), prepend them with a backslash: `\%`, `\_` and `\\`.
The backslash loses its special meaning (i.e. is interpreted literally) if it prepends a character different than `%`, `_` or `\`.

Note

ClickHouse requires backslashes in strings [to be quoted as well](/docs/sql-reference/syntax#string), so you would actually need to write `\\%`, `\\_` and `\\\\`.

For `LIKE` expressions of the form `%needle%`, the function is as fast as the `position` function.
All other LIKE expressions are internally converted to a regular expression and executed with a performance similar to function `match`.

**Syntax**

```
like(haystack, pattern)
-- haystack LIKE pattern
```

**Arguments**

- `haystack` — String in which the search is performed. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `pattern` — `LIKE` pattern to match against. Can contain `%` (matches any number of characters), `_` (matches single character), and `\` for escaping. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Usage example**

```
SELECT like('ClickHouse', '%House');
```

```
┌─like('ClickHouse', '%House')─┐
│                            1 │
└──────────────────────────────┘
```

**Single character wildcard**

```
SELECT like('ClickHouse', 'Click_ouse');
```

```
┌─like('ClickH⋯lick_ouse')─┐
│                        1 │
└──────────────────────────┘
```

**Non-matching pattern**

```
SELECT like('ClickHouse', '%SQL%');
```

```
┌─like('ClickHouse', '%SQL%')─┐
│                           0 │
└─────────────────────────────┘
```

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

Introduced in: v18.16.0

Like [`position`](#position) but with arguments `haystack` and `locate` switched.

Version dependent behavior

The behavior of this function depends on the ClickHouse version:

- in versions < v24.3, `locate` was an alias of function `position` and accepted arguments `(haystack, needle[, start_pos])`.
- in versions >= 24.3, `locate` is an individual function (for better compatibility with MySQL) and accepts arguments `(needle, haystack[, start_pos])`.
  The previous behavior can be restored using setting `function_locate_has_mysql_compatible_argument_order = false`.

**Syntax**

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

**Arguments**

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

**Returned value**

Returns starting position in bytes and counting from 1, if the substring was found, `0`, if the substring was not found. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT locate('ca', 'abcabc')
```

```
┌─locate('ca', 'abcabc')─┐
│                      3 │
└────────────────────────┘
```

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

Introduced in: v1.1.0

Checks if a provided string matches the provided regular expression pattern.

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

Matching works under UTF-8 assumptions, e.g. `¥` uses two bytes internally but matching treats it as a single codepoint.
The regular expression must not contain NULL bytes.
If the haystack or the pattern are not valid UTF-8, the behavior is undefined.

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