String Functions
chr
Returns the Unicode code point n as a single character string.
Examples:
select chr(61) as value; -- value '='
Supported Signatures
function chr(bigint) returns varchar
codepoint
Returns the Unicode code point of the only character of string
.
Examples:
SELECT codepoint('π') AS value; -- value '128522'
Supported Signatures
function codepoint(varchar) returns bigint
concat
Returns the concatenation of string1, string2, β¦, stringN. This function provides the same functionality as the SQL-standard concatenation operator (||).
Examples:
select concat('hello ', 'world!') as value; -- value 'hello world!'
Supported Signatures
function concat($3, array<$3>) returns array<$3>
function concat(array<$3>, ...) returns array<$3>
function concat(array<$3>, $3) returns array<$3>
function concat(varchar, varchar) returns varchar
function concat(varchar, ...) returns varchar
function concat(varbinary, ...) returns varbinary
concat_ws
Using the first array string0
element as separator, returns the concatenation of all subsequent strings string1, string2, β¦ If `string0β is null, then the return value is null. Any null values provided in the arguments after the separator are skipped.
Examples:
SELECT concat_ws('π', 'hey', 'there', 'people') AS value; -- value 'heyπthereπpeople'
Supported Signatures
function concat_ws(varchar, array<varchar>) returns varchar
function concat_ws(varchar, ...) returns varchar
from_utf8
Decodes a UTF-8 encoded string from binary. Invalid UTF-8 sequences are replaced with the Unicode replacement character U+FFFD.
Supported Signatures
function from_utf8(varbinary) returns varchar
function from_utf8(varbinary, bigint) returns varchar
function from_utf8(varbinary, varchar) returns varchar
hamming_distance
Returns the Hamming distance of string1 and string2, i.e. the number of positions at which the corresponding characters are different. Note that the two strings must have the same length.
Supported Signatures
function hamming_distance(varchar, varchar) returns bigint
length
Returns the length of string in characters.
Examples:
select length('Hello World!') as value; -- value '12'
Supported Signatures
function length(varchar) returns bigint
function length(varbinary) returns bigint
levenshtein_distance
Returns the Levenshtein edit distance of string1 and string2, i.e. the minimum number of single-character edits (insertions, deletions or substitutions) needed to change string1 into string2.
Supported Signatures
function levenshtein_distance(varchar, varchar) returns bigint
lower
Converts string to lowercase.
Examples:
select lower('Hello World!') as value; -- value 'hello world!'
Supported Signatures
function lower(varchar) returns varchar
lpad
Left pads string to size characters with padstring. If size is less than the length of string, the result is truncated to size characters. size must not be negative and padstring must be non-empty.
Supported Signatures
function lpad(varbinary, bigint, varbinary) returns varbinary
function lpad(varchar, bigint, varchar) returns varchar
ltrim
Removes leading whitespace from string.
Examples:
select ltrim(' π') as value; -- value 'π'
Supported Signatures
function ltrim(varchar) returns varchar
function ltrim(varchar, codepoints) returns varchar
luhn_check
Tests whether a string of digits is valid according to the Luhn algorithm.
Supported Signatures
function luhn_check(varchar) returns boolean
normalize
Transforms string with NFC normalization form.
Supported Signatures
function normalize(varchar, varchar) returns varchar
replace
Removes all instances of search from string.
Examples:
select replace('Hello There', 'There') as value; -- value 'Hello '
Supported Signatures
function replace(varchar, varchar, varchar) returns varchar
function replace(varchar, varchar) returns varchar
reverse
Returns string with the characters in reverse order.
Examples:
select reverse('Hello There') as value; -- value 'ereht olleH'
Supported Signatures
function reverse(array<$3>) returns array<$3>
function reverse(varbinary) returns varbinary
function reverse(varchar) returns varchar
rpad
Right pads string to size characters with padstring. If size is less than the length of string, the result is truncated to size characters. size must not be negative and padstring must be non-empty.
Supported Signatures
function rpad(varbinary, bigint, varbinary) returns varbinary
function rpad(varchar, bigint, varchar) returns varchar
rtrim
Removes trailing whitespace from string.
Examples:
select rtrim('π ') as value; -- value 'π'
Supported Signatures
function rtrim(varchar) returns varchar
function rtrim(varchar, codepoints) returns varchar
soundex
It is typically used to evaluate the similarity of two expressions phonetically, that is how the string sounds when spoken.
Supported Signatures
function soundex(varchar) returns varchar
split
Splits string on delimiter and returns an array.
Examples:
SELECT split('hello+world', '+') as value; -- value '[hello, world]'
Supported Signatures
function split(varchar, varchar) returns array<varchar>
function split(varchar, varchar, bigint) returns array<varchar>
split_part
Splits string on delimiter and returns the field index. Field indexes start with 1. If the index is larger than the number of fields, then null is returned.
Examples:
SELECT split_part('hello world', 'world', 1) as value; -- value 'hello'
Supported Signatures
function split_part(varchar, varchar, bigint) returns varchar
split_to_map
Splits string by entryDelimiter and keyValueDelimiter and returns a map. entryDelimiter splits string into key-value pairs. keyValueDelimiter splits each pair into key and value.
Supported Signatures
function split_to_map(varchar, varchar, varchar) returns map<varchar, varchar>
split_to_multimap
Splits string by entryDelimiter and keyValueDelimiter and returns a map containing an array of values for each unique key. entryDelimiter splits string into key-value pairs. keyValueDelimiter splits each pair into key and value. The values for each key will be in the same order as they appeared in string.
Supported Signatures
function split_to_multimap(varchar, varchar, varchar) returns map<varchar, array<varchar>>
starts_with
Tests whether substring is a prefix of string.
Examples:
SELECT starts_with('hello world', 'world') -- value 'false'
SELECT starts_with('hello world', 'hello') ; -- value 'true'
Supported Signatures
function starts_with(varchar, varchar) returns boolean
strpos
Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.
Examples:
select strpos('Hello World', 'World') as value; -- value '7'
Supported Signatures
function strpos(varchar, varchar) returns bigint
function strpos(varchar, varchar, bigint) returns bigint
substr
This is an alias for substring().
Examples:
select substr('Hello World', 6) as value; -- value ' World'
Supported Signatures
function substr(varchar, bigint) returns varchar
function substr(varchar, bigint, bigint) returns varchar
function substr(varbinary, bigint) returns varbinary
function substr(varbinary, bigint, bigint) returns varbinary
substring
Returns the rest of string from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of the string.
Examples:
select substring('Hello World', 6) as value; -- value ' World'
Supported Signatures
function substring(varchar, bigint) returns varchar
function substring(varchar, bigint, bigint) returns varchar
to_utf8
Encodes string into a UTF-8 varbinary representation.
Supported Signatures
function to_utf8(varchar) returns varbinary
translate
Returns the source string translated by replacing characters found in the from string with the corresponding characters in the to string. If the from string contains duplicates, only the first is used. If the source character does not exist in the from string, the source character will be copied without translation. If the index of the matching character in the from string is beyond the length of the to string, the source character will be omitted from the resulting string.
Examples:
select translate('Hello World', 'Hell', 'N') as value; -- value 'No Word'
Supported Signatures
function translate(varchar, varchar, varchar) returns varchar
trim
Removes any leading and/or trailing characters as specified up to and including string from source.
Examples:
select trim(' π ') as value; -- value 'π'
Supported Signatures
function trim(varchar) returns varchar
function trim(varchar, codepoints) returns varchar
upper
Converts string to uppercase.
Examples:
select upper('Hello World!') as value; -- value 'HELLO WORLD!'
Supported Signatures
function upper(varchar) returns varchar
word_stem
Returns the stem of word in the English language.
Supported Signatures
function word_stem(varchar) returns varchar
function word_stem(varchar, varchar) returns varchar