Skip to main content
Functions for locating tokens, substrings, subsequences, and regex or fuzzy matches inside a text field. These complement like/ilike/~ (documented on the String functions page) when you need multi-needle, token-aware, or similarity-based matching.

​
hasToken

Checks whether haystack contains token as a whole token. A token is a maximal run of alphanumeric characters, so the token must be surrounded by non-alphanumeric delimiters to match (an underscore acts as a separator). token must be a constant string. Syntax
Arguments
  • haystack — the string to search within.
  • token — the constant token to look for, delimited by non-alphanumeric characters.
Returned value
  • UInt8: 1 if the token is present, otherwise 0.
Example
Find users whose bio contains the word as a token

​
hasTokenCaseInsensitive

Case-insensitive variant of hasToken. Checks whether haystack contains token as a whole token, ignoring case. token must be a constant string. Syntax
Arguments
  • haystack — the string to search within.
  • token — the constant token to look for, matched case-insensitively.
Returned value
  • UInt8: 1 if the token is present regardless of case, otherwise 0.
Example
Match as a token in the bio

​
multiSearchAny

Checks whether haystack contains at least one of the supplied needles as a literal substring (case-sensitive). Syntax
Arguments
  • haystack — the string to search within.
  • [needle1, needle2, ...] — an array of literal substrings to look for.
Returned value
  • UInt8: 1 if any needle is found as a substring, otherwise 0.
Example
Match bios mentioning any leadership

​
multiSearchAnyCaseInsensitive

Case-insensitive variant of multiSearchAny. Checks whether haystack contains at least one of the supplied needles as a literal substring, ignoring case. Syntax
Arguments
  • haystack — the string to search within.
  • [needle1, needle2, ...] — an array of literal substrings, matched case-insensitively.
Returned value
  • UInt8: 1 if any needle is found regardless of case, otherwise 0.
Example
Match bios mentioning a leadership in any casing

​
multiMatchAny

Checks whether haystack matches at least one of the supplied regular expressions. Patterns use RE2 syntax; common patterns behave the same as PostgreSQL POSIX ERE, but exotic POSIX-only constructs may diverge. Syntax
Arguments
  • haystack — the string to search within.
  • [pattern1, pattern2, ...] — an array of RE2 regular expression patterns.
Returned value
  • UInt8: 1 if any pattern matches, otherwise 0.
Example
Match users on a Gmail or Yahoo email domain

​
hasSubsequence

Checks whether needle appears as a subsequence of haystack — its characters occur in order, but not necessarily contiguously (ClickHouse only). Syntax
Arguments
  • haystack — the string to search within.
  • needle — the string whose characters must appear in order within haystack.
Returned value
  • UInt8: 1 if needle is a subsequence of haystack, otherwise 0.
Example
Match emails whose local part contains the ordered letters

​
ngramSearch

Computes a non-symmetric 4-gram similarity ratio of needle within haystack. Higher values indicate that more of needle is present, making it useful for ranking fuzzy matches (ClickHouse only). Syntax
Arguments
  • haystack — the string to search within.
  • needle — the string to look for.
Returned value
  • Float32 in the range [0, 1]. Values closer to 1 indicate that needle is more present in haystack.
Example
Rank bios by how strongly they resemble

​
ngramDistance

Computes the 4-gram edit distance between haystack and needle, based on the symmetric difference of their n-gram multisets. Useful for fuzzy matching and typo tolerance (ClickHouse only). Syntax
Arguments
  • haystack — the string to compare.
  • needle — the string to compare against.
Returned value
  • Float32 in the range [0, 1]. A value of 0 means the strings are identical; smaller values indicate greater similarity.
Example
Find bios that are a close fuzzy match to