Functions for Searching in Strings

**Syntax**

```
notILike(haystack, pattern)
```

**Arguments**

- `haystack` — The input string to search in. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `pattern` — The SQL LIKE pattern to match against. `%` matches any number of characters (including zero), `_` matches exactly one character. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Usage example**

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

```
┌─notILike('Cl⋯ '%house%')─┐
│                        0 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Similar to [`like`](#like) but negates the result.

**Syntax**

```
notLike(haystack, pattern)
-- haystack NOT 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. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Usage example**

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

```
┌─notLike('Cli⋯ '%House%')─┐
│                        0 │
└──────────────────────────┘
```

**Non-matching pattern**

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

```
┌─notLike('Cli⋯', '%SQL%')─┐
│                        1 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Returns the position (in bytes, starting at 1) of a substring `needle` in a string `haystack`.

If substring `needle` is empty, these rules apply:

- if no `start_pos` was specified: return `1`
- if `start_pos = 0`: return `1`
- if `start_pos >= 1` and `start_pos <= length(haystack) + 1`: return `start_pos`
- otherwise: return `0`

The same rules also apply to functions [`locate`](#locate), [`positionCaseInsensitive`](#positionCaseInsensitive), [`positionUTF8`](#positionUTF8) and [`positionCaseInsensitiveUTF8`](#positionCaseInsensitiveUTF8).

**Syntax**

```
position(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. Optional. [`UInt`](/docs/sql-reference/data-types/int-uint)

**Returned value**

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

**Examples**

**Basic usage**

```
SELECT position('Hello, world!', '!')
```

```
┌─position('Hello, world!', '!')─┐
│                             13 │
└────────────────────────────────┘
```

**With start\_pos argument**

```
SELECT position('Hello, world!', 'o', 1), position('Hello, world!', 'o', 7)
```

```
┌─position('Hello, world!', 'o', 1)─┬─position('Hello, world!', 'o', 7)─┐
│                                 5 │                                 9 │
└───────────────────────────────────┴───────────────────────────────────┘
```

**Needle IN haystack syntax**

```
SELECT 6 = position('/' IN s) FROM (SELECT 'Hello/World' AS s)
```

```
┌─equals(6, position(s, '/'))─┐
│                           1 │
└─────────────────────────────┘
```

**Empty needle substring**

```
SELECT position('abc', ''), position('abc', '', 0), position('abc', '', 1), position('abc', '', 2), position('abc', '', 3), position('abc', '', 4), position('abc', '', 5)
```

```
┌─position('abc', '')─┬─position('abc', '', 0)─┬─position('abc', '', 1)─┬─position('abc', '', 2)─┬─position('abc', '', 3)─┬─position('abc', '', 4)─┬─position('abc', '', 5)─┐
│                   1 │                      1 │                      1 │                      2 │                      3 │                      4 │                      0 │
└─────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┘
```

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

Introduced in: v1.1.0

Like [`position`](#position) but case-insensitive.

**Syntax**

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

**Aliases**: `instr`

**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 starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Case insensitive search**

```
SELECT positionCaseInsensitive('Hello, world!', 'hello')
```

```
┌─positionCaseInsensitive('Hello, world!', 'hello')─┐
│                                                 1 │
└───────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
positionCaseInsensitiveUTF8(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 starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Case insensitive UTF-8 search**

```
SELECT positionCaseInsensitiveUTF8('Привет мир', 'МИР')
```

```
┌─positionCaseInsensitiveUTF8('Привет мир', 'МИР')─┐
│                                                8 │
└──────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
positionUTF8(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 starting position in bytes and counting from 1, if the substring was found, otherwise `0`, if the substring was not found. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**UTF-8 character counting**

```
SELECT positionUTF8('Motörhead', 'r')
```

```
┌─position('Motörhead', 'r')─┐
│                          5 │
└────────────────────────────┘
```