Text Functions

This section describes functions and operators for examining and manipulating string values. denotes a space character.

Function Description Example Result
string || string String concatenation 'Duck' || 'DB' DuckDB
ascii(string) Returns an integer that represents the Unicode code point of the first character of the string ascii('Ω') 937
concat(string, ...) Concatenate many strings together concat('Hello', ' ', 'World') Hello World
concat_ws(separator, string, ...) Concatenate strings together separated by the specified separator concat_ws(',', 'Banana', 'Apple', 'Melon') Banana,Apple,Melon
format(format, parameters...) Formats a string using fmt syntax format('Benchmark "{}" took {} seconds', 'CSV', 42) Benchmark "CSV" took 42 seconds
left(string, count) Extract the left-most count characters left('hello', 2) he
length(string) Number of characters in string length('Hello') 5
string LIKE target Returns true if the string matches the like specifier (see Pattern Matching) 'hello' LIKE '%lo' true
lower(string) Convert string to lower case lower('Hello') hello
lpad(string, count, character) Pads the string with the character from the left until it has count characters lpad('hello', 10, '>') >>>>>hello
ltrim(string) Removes any spaces from the left side of the string ltrim('␣␣␣␣test␣␣') test␣␣
ltrim(string, characters) Removes any occurrences of any of the characters from the left side of the string ltrim('>>>>test<<', '><') test<<
upper(string) Convert string to upper case upper('Hello') HELLO
printf(format, parameters...) Formats a string using printf syntax printf('Benchmark "%s" took %d seconds', 'CSV', 42) Benchmark "CSV" took 42 seconds
regexp_full_match(string, regex) Returns true if the entire string matches the regex (see Pattern Matching) regexp_full_match('anabanana', '(an)*') false
regexp_matches(string, regex) Returns true if a part of string matches the regex (see Pattern Matching) regexp_matches('anabanana', '(an)*') true
regexp_replace(string, regex, replacement, modifiers) Replaces the first occurrence of regex with the replacement, use 'g' modifier to replace all occurrences instead (see Pattern Matching) select regexp_replace('hello', '[lo]', '-') he-lo
repeat(string, count) Repeats the string count number of times repeat('A', 5) AAAAA
replace(string, source, target) Replaces any occurrences of the source with target in string replace('hello', 'l', '-') he--o
reverse(string) Reverses the string reverse('hello') olleh
right(string, count) Extract the right-most count characters right('hello', 3) llo
rpad(string, count, character) Pads the string with the character from the right until it has count characters rpad('hello', 10, '<') hello<<<<<
rtrim(string) Removes any spaces from the right side of the string rtrim('␣␣␣␣test␣␣') ␣␣␣␣test
rtrim(string, characters) Removes any occurrences of any of the characters from the right side of the string rtrim('>>>>test<<', '><') >>>>test
string SIMILAR TO regex Returns true if the string matches the regex; identical to regexp_full_match (see Pattern Matching) 'hello' SIMILAR TO 'l+' false
strlen(string) Number of bytes in string length('🤦🏼‍♂️') 1
strip_accents(string) Strips accents from string strip_accents('mühleisen') muhleisen
string_split(string, separator) Splits the string along the separator string_split('hello␣world', '␣') ['hello', 'world']
string_split_regex(string, regex) Splits the string along the regex string_split_regex('hello␣world; 42', ';?␣') ['hello', 'world', '42']
substring(string, start, length) Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string. substring('Hello', 2, 2) el
trim(string) Removes any spaces from either side of the string trim('␣␣␣␣test␣␣') test
trim(string, characters) Removes any occurrences of any of the characters from either side of the string trim('>>>>test<<', '><') test
unicode(string) Returns the unicode code of the first character of the string unicode('ü') 252