String and regular expression functions
These functions are available in Telemetry SQL. Signatures, aliases, argument descriptions, and examples below follow the vendored engine fork. Examples that name a table assume matching columns in your own table; result grids illustrate values and may differ from the dashboard or JSON display.
SQL reference · Scalar functions · Date and time functions · Array, struct, and map functions · Aggregate functions · Window functions
String Functions
- ascii
- bit_length
- btrim
- char_length
- character_length
- chr
- concat
- concat_ws
- contains
- ends_with
- find_in_set
- initcap
- instr
- left
- length
- levenshtein
- lower
- lpad
- ltrim
- octet_length
- overlay
- position
- repeat
- replace
- reverse
- right
- rpad
- rtrim
- split_part
- starts_with
- strpos
- substr
- substr_index
- substring
- substring_index
- to_hex
- translate
- trim
- upper
- uuid
ascii
Returns the first Unicode scalar value of a string.
ascii(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select ascii('abc');
+--------------------+
| ascii(Utf8("abc")) |
+--------------------+
| 97 |
+--------------------+
select ascii('🚀');
+-------------------+
| ascii(Utf8("🚀")) |
+-------------------+
| 128640 |
+-------------------+
Related functions:
bit_length
Returns the bit length of a string.
bit_length(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select bit_length('datafusion');
+--------------------------------+
| bit_length(Utf8("datafusion")) |
+--------------------------------+
| 80 |
+--------------------------------+
Related functions:
btrim
Trims the specified trim string from the start and end of a string. If no trim string is provided, all spaces are removed from the start and end of the input string.
btrim(str[, trim_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- trim_str: String expression to operate on. Can be a constant, column, or function, and any combination of operators. Default is a space.
Example
select btrim('__datafusion____', '_');
+-------------------------------------------+
| btrim(Utf8("__datafusion____"),Utf8("_")) |
+-------------------------------------------+
| datafusion |
+-------------------------------------------+
Alternative Syntax
trim(BOTH trim_str FROM str)
trim(trim_str FROM str)
Aliases
- trim
Related functions:
char_length
Alias of character_length.
character_length
Returns the number of characters in a string.
character_length(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select character_length('Ångström');
+------------------------------------+
| character_length(Utf8("Ångström")) |
+------------------------------------+
| 8 |
+------------------------------------+
Aliases
- length
- char_length
Related functions:
chr
Returns a string containing the character with the specified Unicode scalar value.
chr(expression)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select chr(128640);
+--------------------+
| chr(Int64(128640)) |
+--------------------+
| 🚀 |
+--------------------+
Related functions:
concat
Concatenates multiple strings together.
concat(str[, ..., str_n])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- str_n: Subsequent string expressions to concatenate.
Example
select concat('data', 'f', 'us', 'ion');
+-------------------------------------------------------+
| concat(Utf8("data"),Utf8("f"),Utf8("us"),Utf8("ion")) |
+-------------------------------------------------------+
| datafusion |
+-------------------------------------------------------+
Related functions:
concat_ws
Concatenates multiple strings together with a specified separator.
concat_ws(separator, str[, ..., str_n])
Arguments
- separator: Separator to insert between concatenated strings.
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- str_n: Subsequent string expressions to concatenate.
Example
select concat_ws('_', 'data', 'fusion');
+--------------------------------------------------+
| concat_ws(Utf8("_"),Utf8("data"),Utf8("fusion")) |
+--------------------------------------------------+
| data_fusion |
+--------------------------------------------------+
Related functions:
contains
Return true if search_str is found within string (case-sensitive).
contains(str, search_str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- search_str: The string to search for in str.
Example
select contains('the quick brown fox', 'row');
+---------------------------------------------------+
| contains(Utf8("the quick brown fox"),Utf8("row")) |
+---------------------------------------------------+
| true |
+---------------------------------------------------+
ends_with
Tests if a string ends with a substring.
ends_with(str, substr)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- substr: Substring to test for.
Example
select ends_with('datafusion', 'soin');
+--------------------------------------------+
| ends_with(Utf8("datafusion"),Utf8("soin")) |
+--------------------------------------------+
| false |
+--------------------------------------------+
select ends_with('datafusion', 'sion');
+--------------------------------------------+
| ends_with(Utf8("datafusion"),Utf8("sion")) |
+--------------------------------------------+
| true |
+--------------------------------------------+
find_in_set
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.
find_in_set(str, strlist)
Arguments
- str: String expression to find in strlist.
- strlist: A string list is a string composed of substrings separated by , characters.
Example
select find_in_set('b', 'a,b,c,d');
+----------------------------------------+
| find_in_set(Utf8("b"),Utf8("a,b,c,d")) |
+----------------------------------------+
| 2 |
+----------------------------------------+
initcap
Capitalizes the first character in each word in the input string. Words are delimited by non-alphanumeric characters.
initcap(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select initcap('apache datafusion');
+------------------------------------+
| initcap(Utf8("apache datafusion")) |
+------------------------------------+
| Apache Datafusion |
+------------------------------------+
Related functions:
instr
Alias of strpos.
left
Returns a specified number of characters from the left side of a string.
left(str, n)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- n: Number of characters to return.
Example
select left('datafusion', 4);
+-----------------------------------+
| left(Utf8("datafusion"),Int64(4)) |
+-----------------------------------+
| data |
+-----------------------------------+
Related functions:
length
Alias of character_length.
levenshtein
Returns the Levenshtein distance between the two given strings.
levenshtein(str1, str2)
Arguments
- str1: String expression to compute Levenshtein distance with str2.
- str2: String expression to compute Levenshtein distance with str1.
Example
select levenshtein('kitten', 'sitting');
+---------------------------------------------+
| levenshtein(Utf8("kitten"),Utf8("sitting")) |
+---------------------------------------------+
| 3 |
+---------------------------------------------+
lower
Converts a string to lower-case.
lower(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select lower('Ångström');
+-------------------------+
| lower(Utf8("Ångström")) |
+-------------------------+
| ångström |
+-------------------------+
Related functions:
lpad
Pads the left side of a string with another string to a specified string length.
lpad(str, n[, padding_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- n: String length to pad to. If the input string is longer than this length, it is truncated (on the right).
- padding_str: Optional string expression to pad with. Can be a constant, column, or function, and any combination of string operators. Default is a space.
Example
select lpad('Dolly', 10, 'hello');
+---------------------------------------------+
| lpad(Utf8("Dolly"),Int64(10),Utf8("hello")) |
+---------------------------------------------+
| helloDolly |
+---------------------------------------------+
Related functions:
ltrim
Trims the specified trim string from the beginning of a string. If no trim string is provided, spaces are removed from the start of the input string.
ltrim(str[, trim_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- trim_str: String expression to trim from the beginning of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is a space.
Example
select ltrim(' datafusion ');
+-------------------------------+
| ltrim(Utf8(" datafusion ")) |
+-------------------------------+
| datafusion |
+-------------------------------+
select ltrim('___datafusion___', '_');
+-------------------------------------------+
| ltrim(Utf8("___datafusion___"),Utf8("_")) |
+-------------------------------------------+
| datafusion___ |
+-------------------------------------------+
Alternative Syntax
trim(LEADING trim_str FROM str)
Related functions:
octet_length
Returns the length of a string in bytes.
octet_length(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select octet_length('Ångström');
+--------------------------------+
| octet_length(Utf8("Ångström")) |
+--------------------------------+
| 10 |
+--------------------------------+
Related functions:
overlay
Returns the string which is replaced by another string from the specified position and specified count length.
overlay(str PLACING substr FROM pos [FOR count])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- substr: Substring to replace in str.
- pos: The start position to start the replace in str.
- count: The count of characters to be replaced from start position of str. If not specified, will use substr length instead.
Example
select overlay('Txxxxas' placing 'hom' from 2 for 4);
+--------------------------------------------------------+
| overlay(Utf8("Txxxxas"),Utf8("hom"),Int64(2),Int64(4)) |
+--------------------------------------------------------+
| Thomas |
+--------------------------------------------------------+
position
Alias of strpos.
repeat
Returns a string with an input string repeated a specified number.
repeat(str, n)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- n: Number of times to repeat the input string.
Example
select repeat('data', 3);
+-------------------------------+
| repeat(Utf8("data"),Int64(3)) |
+-------------------------------+
| datadatadata |
+-------------------------------+
replace
Replaces all occurrences of a specified substring in a string with a new substring.
replace(str, substr, replacement)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- substr: Substring expression to replace in the input string. Substring expression to operate on. Can be a constant, column, or function, and any combination of operators.
- replacement: Replacement substring expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select replace('ABabbaBA', 'ab', 'cd');
+-------------------------------------------------+
| replace(Utf8("ABabbaBA"),Utf8("ab"),Utf8("cd")) |
+-------------------------------------------------+
| ABcdbaBA |
+-------------------------------------------------+
reverse
Reverses the character order of a string.
reverse(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select reverse('datafusion');
+-----------------------------+
| reverse(Utf8("datafusion")) |
+-----------------------------+
| noisufatad |
+-----------------------------+
right
Returns a specified number of characters from the right side of a string.
right(str, n)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- n: Number of characters to return.
Example
select right('datafusion', 6);
+------------------------------------+
| right(Utf8("datafusion"),Int64(6)) |
+------------------------------------+
| fusion |
+------------------------------------+
Related functions:
rpad
Pads the right side of a string with another string to a specified string length.
rpad(str, n[, padding_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- n: String length to pad to. If the input string is longer than this length, it is truncated.
- padding_str: String expression to pad with. Can be a constant, column, or function, and any combination of string operators. Default is a space.
Example
select rpad('datafusion', 20, '_-');
+-----------------------------------------------+
| rpad(Utf8("datafusion"),Int64(20),Utf8("_-")) |
+-----------------------------------------------+
| datafusion_-_-_-_-_- |
+-----------------------------------------------+
Related functions:
rtrim
Trims the specified trim string from the end of a string. If no trim string is provided, all spaces are removed from the end of the input string.
rtrim(str[, trim_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- trim_str: String expression to trim from the end of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is a space.
Example
select rtrim(' datafusion ');
+-------------------------------+
| rtrim(Utf8(" datafusion ")) |
+-------------------------------+
| datafusion |
+-------------------------------+
select rtrim('___datafusion___', '_');
+-------------------------------------------+
| rtrim(Utf8("___datafusion___"),Utf8("_")) |
+-------------------------------------------+
| ___datafusion |
+-------------------------------------------+
Alternative Syntax
trim(TRAILING trim_str FROM str)
Related functions:
split_part
Splits a string based on a specified delimiter and returns the substring in the specified position.
split_part(str, delimiter, pos)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- delimiter: String or character to split on.
- pos: Position of the part to return (counting from 1). Negative values count backward from the end of the string.
Example
select split_part('1.2.3.4.5', '.', 3);
+--------------------------------------------------+
| split_part(Utf8("1.2.3.4.5"),Utf8("."),Int64(3)) |
+--------------------------------------------------+
| 3 |
+--------------------------------------------------+
starts_with
Tests if a string starts with a substring.
starts_with(str, substr)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- substr: Substring to test for.
Example
select starts_with('datafusion','data');
+----------------------------------------------+
| starts_with(Utf8("datafusion"),Utf8("data")) |
+----------------------------------------------+
| true |
+----------------------------------------------+
strpos
Returns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0.
strpos(str, substr)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- substr: Substring expression to search for.
Example
select strpos('datafusion', 'fus');
+----------------------------------------+
| strpos(Utf8("datafusion"),Utf8("fus")) |
+----------------------------------------+
| 5 |
+----------------------------------------+
Alternative Syntax
position(substr in origstr)
Aliases
- instr
- position
substr
Extracts a substring of a specified number of characters from a specific starting position in a string.
substr(str, start_pos[, length])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- start_pos: Character position to start the substring at. The first character in the string has a position of 1. If the start position is less than 1, it is treated as if it is before the start of the string and the (absolute) number of characters before position 1 is subtracted from
length(if given). For example,substr('abc', -3, 6)returns'ab'. - length: Number of characters to extract. If not specified, returns the rest of the string after the start position.
Example
select substr('datafusion', 5, 3);
+----------------------------------------------+
| substr(Utf8("datafusion"),Int64(5),Int64(3)) |
+----------------------------------------------+
| fus |
+----------------------------------------------+
Alternative Syntax
substring(str from start_pos for length)
Aliases
- substring
substr_index
Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
substr_index(str, delim, count)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- delim: The string to find in str to split str.
- count: The number of times to search for the delimiter. Can be either a positive or negative number.
Example
select substr_index('www.apache.org', '.', 1);
+---------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(1)) |
+---------------------------------------------------------+
| www |
+---------------------------------------------------------+
select substr_index('www.apache.org', '.', -1);
+----------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(-1)) |
+----------------------------------------------------------+
| org |
+----------------------------------------------------------+
Aliases
- substring_index
substring
Alias of substr.
substring_index
Alias of substr_index.
to_hex
Converts an integer to a hexadecimal string.
to_hex(int)
Arguments
- int: Integer expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select to_hex(12345689);
+-------------------------+
| to_hex(Int64(12345689)) |
+-------------------------+
| bc6159 |
+-------------------------+
translate
Performs character-wise substitution based on a mapping.
translate(str, from, to)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- from: The characters to be replaced.
- to: The characters to replace them with. Each character in from that is found in str is replaced by the character at the same index in to. Any characters in from that don't have a corresponding character in to are removed. If a character appears more than once in from, the first occurrence determines the mapping.
Example
select translate('twice', 'wic', 'her');
+--------------------------------------------------+
| translate(Utf8("twice"),Utf8("wic"),Utf8("her")) |
+--------------------------------------------------+
| there |
+--------------------------------------------------+
trim
Alias of btrim.
upper
Converts a string to upper-case.
upper(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select upper('dataFusion');
+---------------------------+
| upper(Utf8("dataFusion")) |
+---------------------------+
| DATAFUSION |
+---------------------------+
Related functions:
uuid
Returns UUID v4 string value which is unique per row.
uuid()
Example
select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 6ec17ef8-1934-41cc-8d59-d0c8f9eea1f0 |
+--------------------------------------+
Regular Expression Functions
Telemetry uses a PCRE-like regular expression syntax (minus support for several features including look-around and backreferences). The following regular expression functions are supported:
regexp_count
Returns the number of matches that a regular expression has in a string.
regexp_count(str, regexp[, start, flags])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- regexp: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators.
- start: - start: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function.
- flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
- i: case-insensitive: letters match both upper and lower case
- m: multi-line mode: ^ and $ match begin/end of line
- s: allow . to match \n
- R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
- U: swap the meaning of x* and x*?
Example
select regexp_count('abcAbAbc', 'abc', 2, 'i');
+---------------------------------------------------------------+
| regexp_count(Utf8("abcAbAbc"),Utf8("abc"),Int64(2),Utf8("i")) |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
regexp_instr
Returns the position in a string where the specified occurrence of a POSIX regular expression is located.
regexp_instr(str, regexp[, start[, N[, flags[, subexpr]]]])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- regexp: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators.
- start: - start: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function. Defaults to 1
- N: - N: Optional The N-th occurrence of pattern to find. Defaults to 1 (first match). Can be a constant, column, or function.
- flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
- i: case-insensitive: letters match both upper and lower case
- m: multi-line mode: ^ and $ match begin/end of line
- s: allow . to match \n
- R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
- U: swap the meaning of x* and x*?
- subexpr: Optional Specifies which capture group (subexpression) to return the position for. Defaults to 0, which returns the position of the entire match.
Example
SELECT regexp_instr('ABCDEF', 'C(.)(..)');
+---------------------------------------------------------------+
| regexp_instr(Utf8("ABCDEF"),Utf8("C(.)(..)")) |
+---------------------------------------------------------------+
| 3 |
+---------------------------------------------------------------+
regexp_like
Returns true if a regular expression has at least one match in a string, false otherwise.
regexp_like(str, regexp[, flags])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- regexp: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators.
- flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
- i: case-insensitive: letters match both upper and lower case
- m: multi-line mode: ^ and $ match begin/end of line
- s: allow . to match \n
- R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
- U: swap the meaning of x* and x*?
Example
select regexp_like('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+--------------------------------------------------------+
| regexp_like(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+--------------------------------------------------------+
| true |
+--------------------------------------------------------+
SELECT regexp_like('aBc', '(b|d)', 'i');
+--------------------------------------------------+
| regexp_like(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+--------------------------------------------------+
| true |
+--------------------------------------------------+
regexp_match
Returns the first regular expression matches in a string.
regexp_match(str, regexp[, flags])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- regexp: Regular expression to match against. Can be a constant, column, or function.
- flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
- i: case-insensitive: letters match both upper and lower case
- m: multi-line mode: ^ and $ match begin/end of line
- s: allow . to match \n
- R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
- U: swap the meaning of x* and x*?
Example
> select regexp_match('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+---------------------------------------------------------+
| regexp_match(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+---------------------------------------------------------+
| [Köln] |
+---------------------------------------------------------+
SELECT regexp_match('aBc', '(b|d)', 'i');
+---------------------------------------------------+
| regexp_match(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+---------------------------------------------------+
| [B] |
+---------------------------------------------------+
regexp_replace
Replaces substrings in a string that match a regular expression.
regexp_replace(str, regexp, replacement[, flags])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- regexp: Regular expression to match against. Can be a constant, column, or function.
- replacement: Replacement string expression to operate on. Can be a constant, column, or function, and any combination of operators.
- flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
- g: (global) Search globally and don't return after the first match
- i: case-insensitive: letters match both upper and lower case
- m: multi-line mode: ^ and $ match begin/end of line
- s: allow . to match \n
- R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
- U: swap the meaning of x* and x*?
Example
select regexp_replace('foobarbaz', 'b(..)', 'X\\1Y', 'g');
+------------------------------------------------------------------------+
| regexp_replace(Utf8("foobarbaz"),Utf8("b(..)"),Utf8("X\1Y"),Utf8("g")) |
+------------------------------------------------------------------------+
| fooXarYXazY |
+------------------------------------------------------------------------+
SELECT regexp_replace('aBc', '(b|d)', 'Ab\\1a', 'i');
+-------------------------------------------------------------------+
| regexp_replace(Utf8("aBc"),Utf8("(b|d)"),Utf8("Ab\1a"),Utf8("i")) |
+-------------------------------------------------------------------+
| aAbBac |
+-------------------------------------------------------------------+
Attribution
Adapted from the function documentation in Telemetry's vendored Apache DataFusion fork, with Telemetry-specific additions and corrections. Copyright 2019–2026 The Apache Software Foundation. Distributed under the Apache License 2.0; see the Apache notice.