user_properties ->> 'email' or user_properties ->> 'country', and on the event_name column of events. Unless noted, every function below works on both PostgreSQL and ClickHouse.
lower / upper / lowerUTF8 / upperUTF8
Convert a string to lower or upper case. TheUTF8 variants case-fold multi-byte Unicode characters correctly rather than only ASCII letters.
Syntax
s— the input string.
- The case-converted string.
Normalize email to lower case for matching
|| (concatenation)
Joins two or more string values end to end into a single string. Syntaxa,b, … — the string values to join, evaluated left to right.
- The concatenated string.
Build a display label from first and last name
\|\| when you want to concatenate; each side is coerced to text first.
like / ilike
Pattern-matching operators that test a string against a pattern using% (any sequence of characters) and _ (any single character). like is case-sensitive; ilike is case-insensitive.
Syntax
string— the value to test.pattern— the pattern, using%and_wildcards.
- A boolean: true when the string matches the pattern.
Match users on educational email domains
~ / ~* (regex match)
POSIX regular-expression match operators.~ is case-sensitive; ~* is case-insensitive. The pattern matches anywhere in the string unless anchored with ^ or $.
Syntax
string— the value to test.pattern— the POSIX regular expression.
- A boolean: true when the pattern matches.
Match names beginning with a vowel, ignoring case
position / strpos / locate
Return the 1-based position of the first occurrence of a substring within a string, or0 if it is not found. In the SQL-standard position(needle in haystack) form the needle comes first; in the two-argument function forms strpos(haystack, needle), position(haystack, needle), and locate(haystack, needle) the haystack comes first.
Syntax
haystack— the string to search within.needle— the substring to look for.
- The 1-based index of the first match, or
0if not found (integer).
Find where the domain starts in an email
substring / substringUTF8
Extracts a portion of a string starting at a 1-based offset for an optional length.substringUTF8 counts offsets and lengths in Unicode code points.
Syntax
s— the input string.offset— the 1-based start position.length— optional number of characters to return; without it, the rest of the string is returned.
- The extracted substring.
Take the first three letters of a country
split_part
Splits a string on a delimiter and returns the field at a given 1-based position. Syntaxstring— the input string.delimiter— the substring to split on.n— the 1-based index of the field to return.
- The nth field as a string, or an empty string if there is no such field.
Extract the domain from an email
replace / replaceAll
Replace occurrences of a literal substring with another string.replace and replaceAll both replace every occurrence.
Syntax
haystack— the input string.pattern— the literal substring to find.replacement— the string to substitute in.
- The string with all matches replaced.
Mask the domain separator in an email
concat
Concatenates all of its arguments into one string. Unlike\|\|, it accepts a variable number of arguments in a single call.
Syntax
s1, s2, ...— the values to join, in order.
- The concatenated string.
Combine name and country into one string
concat_ws
Concatenates its arguments using a separator inserted between each pair of values. Syntaxseparator— the string placed between each joined value.s1, s2, ...— the values to join.
- The joined string with
separatorbetween elements.
Join address parts with a comma
starts_with / ends_with
Test whether a string begins or ends with a given substring. Syntaxs— the input string.prefix/suffix— the substring to test for.
- A boolean: true when the string starts (or ends) with the given substring.
Find users with a Gmail address
left / right
Return the leftmost or rightmostn characters of a string. A negative n counts from the other end, returning all but the last (or first) n characters.
Syntax
s— the input string.n— the number of characters to return.
- The extracted substring.
Extract the top-level domain from an email
ltrim / rtrim / btrim / trim
Remove leading, trailing, or both leading and trailing characters from a string.ltrim trims the left side, rtrim the right, and btrim / trim both sides. Without a characters argument, whitespace is removed.
Syntax
s— the input string.characters— optional set of characters to strip; any character in the set is removed from the relevant end(s).
- The trimmed string.
Strip surrounding whitespace from a name
length / lengthUTF8
length returns the number of bytes in a string; lengthUTF8 returns the number of Unicode code points, which is the correct count for text containing multi-byte characters.
Syntax
s— the input string.
- The length as an integer.
Count characters in each user's name
lpad / rpad
Pad a string to a target length by adding fill characters on the left (lpad) or right (rpad). If the string is already longer than the target, it is truncated to that length.
Syntax
s— the input string.length— the target length in characters.fill— optional padding string; defaults to a space.
- The padded (or truncated) string.
Right-pad country codes to a fixed width
leftPadUTF8 / rightPadUTF8
Unicode-aware padding: likelpad / rpad, but the target length is measured in Unicode code points rather than bytes.
Syntax
s— the input string.length— the target length in code points.fill— optional padding string; defaults to a space.
- The padded (or truncated) string.
Left-pad a name to ten code points
regexp_replace / replaceRegexpOne / replaceRegexpAll
Replace text matched by a regular expression.replaceRegexpOne replaces only the first match; replaceRegexpAll replaces every match. regexp_replace replaces the first match by default, or all matches when the g flag is passed.
Syntax
source/haystack— the input string.pattern— the regular expression to match.replacement— the replacement string; may reference capture groups.flags— optionalregexp_replaceflags, such asgfor global replacement.
- The string with matches replaced.
Strip all digits from a phone number
regexp_count
Counts the number of times a regular expression matches within a string, optionally starting from a given position. Syntaxstring— the input string.pattern— the regular expression to match.start— optional 1-based position to begin searching from.flags— optional matching flags, such asifor case-insensitive.
- The number of matches (integer).
Count vowels in a name
extractAll / regexpExtract
extractAll returns every non-overlapping match of a regular expression as an array. regexpExtract returns a single match: with an index argument it returns that capture group (index 0 returns the whole match).
Syntax
haystack— the input string.pattern— the regular expression to match.index— forregexpExtract, the capture group to return;0(the default) returns the entire match.
extractAllreturns an array of strings;regexpExtractreturns a single string.
Pull all digit groups out of a phone number
initcap
Capitalizes the first letter of each word and lower-cases the rest. Word boundaries are non-alphanumeric characters. Syntaxs— the input string.
- The title-cased string.
Title-case a user's name
reverse / reverseUTF8
Reverses the order of characters in a string.reverse operates on bytes; reverseUTF8 reverses whole Unicode code points.
Syntax
s— the input string.
- The reversed string.
Reverse a country code
translate / translateUTF8
Replace individual characters according to a from/to mapping: each character infrom is replaced by the character at the same position in to. translateUTF8 maps whole Unicode code points.
Syntax
s— the input string.from— the set of characters to replace.to— the replacement characters, positionally aligned withfrom.
- The translated string.
Transliterate vowels in a name
overlay
Replaces a portion of a string with another string, starting at a given position for an optional length. Syntaxstring— the input string.substring— the replacement text inserted atstart.start— the 1-based position where replacement begins.length— optional number of characters to replace; defaults to the length ofsubstring.
- The modified string.
Mask the start of a country code
repeat
Builds a string by repeating the input a given number of times. Syntaxs— the string to repeat.n— the number of repetitions.
- The repeated string.
Repeat a separator character
ascii / chr
ascii returns the numeric code of the first character of a string. chr is the inverse: it returns the single-character string for a given code point.
Syntax
s— forascii, the input string.n— forchr, the character code.
asciireturns an integer code;chrreturns a one-character string.
Get the code of the first letter of a country
positionCaseInsensitive / positionUTF8
Variants ofposition that ignore case (positionCaseInsensitive) or count the returned position in Unicode code points rather than bytes (positionUTF8).
Syntax
haystack— the string to search within.needle— the substring to look for.
- The 1-based index of the first match, or
0if not found (integer).
Case-insensitively locate a country name
countSubstrings / countSubstringsCaseInsensitive
Count how many times a literal substring occurs in a string.countSubstringsCaseInsensitive ignores case.
Syntax
haystack— the string to search within.needle— the literal substring to count.
- The number of non-overlapping occurrences (integer).
Count dots in an email address
countMatches / countMatchesCaseInsensitive
Count how many times a regular expression matches in a string.countMatchesCaseInsensitive matches without regard to case.
Syntax
haystack— the string to search within.pattern— the regular expression to match.
- The number of non-overlapping matches (integer).
Count digit groups in a phone number
string_to_array
Splits a string on a delimiter and returns all fields as an array (PostgreSQL only). Syntaxstring— the input string.delimiter— the substring to split on.null_string— optional; fields equal to this value becomeNULL.
- An array of strings.
Split an email into local part and domain
splitByChar / splitByString
Split a string into an array of substrings.splitByChar splits on a single-character separator; splitByString splits on a multi-character separator.
Syntax
separator— the separator to split on (a single character forsplitByChar).s— the string to split.max_substrings— optional cap on the number of returned elements.
- An array of strings.
Split an email on the @ sign
splitByRegexp
Splits a string into an array of substrings using a regular expression as the separator. Syntaxregexp— the regular expression separator.s— the string to split.max_substrings— optional cap on the number of returned elements.
- An array of strings.
Split a name on any run of whitespace or punctuation
splitByWhitespace / splitByNonAlpha
Split a string into an array of substrings.splitByWhitespace splits on runs of whitespace; splitByNonAlpha splits on runs of non-alphanumeric characters.
Syntax
s— the string to split.max_substrings— optional cap on the number of returned elements.
- An array of strings.
Split a full name into words
alphaTokens
Splits a string into an array of runs of consecutive alphabetic (a–z, A–Z) characters, discarding everything else. Syntaxs— the input string.max_substrings— optional cap on the number of returned elements.
- An array of alphabetic substrings.
Extract the alphabetic words from an email
tokens
Splits a string into an array of tokens. By default it tokenizes on non-alphanumeric separators, which is useful for turning free text into searchable terms. Syntaxs— the input string.
- An array of token strings.
Tokenize a user's name
appendTrailingCharIfAbsent
Appends a character to the end of a string only if the string does not already end with it. Useful for ensuring a value has a trailing separator. Syntaxs— the input string.c— the single character to append if absent.
- The string, guaranteed to end with
c.
Ensure a path value ends with a slash
bar
Renders a numeric value as a horizontal bar drawn with Unicode block characters, scaled between a minimum and maximum. Handy for quick inline visualizations in query output. Syntaxx— the value to render.min— the value mapped to an empty bar.max— the value mapped to a full-width bar.width— optional bar width in characters; defaults to 80.
- A string of block characters representing
xon the scale.
Visualize a score on a 0-to-100 scale
formatReadableSize / formatReadableDecimalSize
Render a byte count as a human-readable size string.formatReadableSize uses binary units (KiB, MiB, …); formatReadableDecimalSize uses decimal units (KB, MB, …).
Syntax
value— the size in bytes.precision— optional number of decimal digits; defaults to 2.
- A formatted size string with a unit suffix.
Show storage used per user in readable units
formatReadableQuantity
Renders a number as a human-readable quantity with a word suffix, such as “thousand” or “million”. Syntaxvalue— the number to format.precision— optional number of decimal digits; defaults to 2.
- A formatted quantity string.
Format a follower count for display
formatReadableTimeDelta
Renders a number of seconds as a human-readable duration, such as “1 hour 1 minute”. Syntaxseconds— the duration in seconds.maximum_unit— optional largest unit to display; defaults to years.minimum_unit— optional smallest unit to display; defaults to seconds.
- A formatted duration string.
Show session length in readable form
similar to
Tests a string against a SQL-standard regular expression pattern, which blendsLIKE wildcards with regular-expression constructs such as |, *, and +.
Syntax
string— the value to test.pattern— the SQL regular-expression pattern.
- A boolean: true when the whole string matches the pattern.
Match users in India or the US