Functions for Searching in Strings

**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 │
└────────────────────────────┘
```

- [countMatches](#countMatches)- [countMatchesCaseInsensitive](#countMatchesCaseInsensitive)- [countSubstrings](#countSubstrings)- [countSubstringsCaseInsensitive](#countSubstringsCaseInsensitive)- [countSubstringsCaseInsensitiveUTF8](#countSubstringsCaseInsensitiveUTF8)- [extract](#extract)- [extractAll](#extractAll)- [extractAllGroupsHorizontal](#extractAllGroupsHorizontal)- [extractGroups](#extractGroups)- [hasAllTokens](#hasAllTokens)- [hasAnyTokens](#hasAnyTokens)- [hasPhrase](#hasPhrase)- [hasSubsequence](#hasSubsequence)- [hasSubsequenceCaseInsensitive](#hasSubsequenceCaseInsensitive)- [hasSubsequenceCaseInsensitiveUTF8](#hasSubsequenceCaseInsensitiveUTF8)- [hasSubsequenceUTF8](#hasSubsequenceUTF8)- [hasToken](#hasToken)- [hasTokenCaseInsensitive](#hasTokenCaseInsensitive)- [hasTokenCaseInsensitiveOrNull](#hasTokenCaseInsensitiveOrNull)- [hasTokenOrNull](#hasTokenOrNull)- [highlight](#highlight)- [ilike](#ilike)- [like](#like)- [locate](#locate)- [match](#match)- [multiFuzzyMatchAllIndices](#multiFuzzyMatchAllIndices)- [multiFuzzyMatchAny](#multiFuzzyMatchAny)- [multiFuzzyMatchAnyIndex](#multiFuzzyMatchAnyIndex)- [multiMatchAllIndices](#multiMatchAllIndices)- [multiMatchAny](#multiMatchAny)- [multiMatchAnyIndex](#multiMatchAnyIndex)- [multiSearchAllPositions](#multiSearchAllPositions)- [multiSearchAllPositionsCaseInsensitive](#multiSearchAllPositionsCaseInsensitive)- [multiSearchAllPositionsCaseInsensitiveUTF8](#multiSearchAllPositionsCaseInsensitiveUTF8)- [multiSearchAllPositionsUTF8](#multiSearchAllPositionsUTF8)- [multiSearchAny](#multiSearchAny)- [multiSearchAnyCaseInsensitive](#multiSearchAnyCaseInsensitive)- [multiSearchAnyCaseInsensitiveUTF8](#multiSearchAnyCaseInsensitiveUTF8)- [multiSearchAnyUTF8](#multiSearchAnyUTF8)- [multiSearchFirstIndex](#multiSearchFirstIndex)- [multiSearchFirstIndexCaseInsensitive](#multiSearchFirstIndexCaseInsensitive)- [multiSearchFirstIndexCaseInsensitiveUTF8](#multiSearchFirstIndexCaseInsensitiveUTF8)- [multiSearchFirstIndexUTF8](#multiSearchFirstIndexUTF8)- [multiSearchFirstPosition](#multiSearchFirstPosition)- [multiSearchFirstPositionCaseInsensitive](#multiSearchFirstPositionCaseInsensitive)- [multiSearchFirstPositionCaseInsensitiveUTF8](#multiSearchFirstPositionCaseInsensitiveUTF8)- [multiSearchFirstPositionUTF8](#multiSearchFirstPositionUTF8)- [ngramDistance](#ngramDistance)- [ngramDistanceCaseInsensitive](#ngramDistanceCaseInsensitive)- [ngramDistanceCaseInsensitiveUTF8](#ngramDistanceCaseInsensitiveUTF8)- [ngramDistanceUTF8](#ngramDistanceUTF8)- [ngramSearch](#ngramSearch)- [ngramSearchCaseInsensitive](#ngramSearchCaseInsensitive)- [ngramSearchCaseInsensitiveUTF8](#ngramSearchCaseInsensitiveUTF8)- [ngramSearchUTF8](#ngramSearchUTF8)- [notILike](#notILike)- [notLike](#notLike)- [position](#position)- [positionCaseInsensitive](#positionCaseInsensitive)- [positionCaseInsensitiveUTF8](#positionCaseInsensitiveUTF8)- [positionUTF8](#positionUTF8)

Was this page helpful?