String Functions¶
Note
These functions assume that the input strings contain valid UTF-8 encoded Unicode code points. There are no explicit checks for valid UTF-8 and the functions may return incorrect results on invalid UTF-8.
Additionally, the functions operate on Unicode code points and not user visible characters (or grapheme clusters). Some languages combine multiple code points into a single user-perceived character, the basic unit of a writing system for a language, but the functions will treat each code point as a separate unit.
The lower()
and upper()
functions do not perform
locale-sensitive, context-sensitive, or one-to-many mappings required for
some languages. Specifically, this will return incorrect results for
Lithuanian, Turkish and Azeri.
- chr(n) varchar ¶
Returns the Unicode code point
n
as a single character string.
- codepoint(string) integer ¶
Returns the Unicode code point of the only character of
string
.
- concat(string1, ..., stringN) varchar ¶
Returns the concatenation of
string1
,string2
,...
,stringN
. This function provides the same functionality as the SQL-standard concatenation operator (||
).
- from_utf8(binary) varchar ¶
Decodes a UTF-8 encoded string from
binary
. Invalid UTF-8 sequences are replaced with the Unicode replacement characterU+FFFD
.
- from_utf8(binary, replace) varchar
Decodes a UTF-8 encoded string from
binary
. Invalid UTF-8 sequences are replaced with replace. The replace argument can be either Unicode code point (bigint), a single character or empty string. When replace is an empty string invalid characters are removed.
- length(string) bigint ¶
Returns the length of
string
in characters.
- levenshtein_distance(string_1, string_2) bigint ¶
Returns the Levenshtein edit distance of 2 strings. I.e. the minimum number of single-character edits (insertions, deletions or substitutions) needed to convert
string_1
tostring_2
.
- lower(string) varchar ¶
Converts
string
to lowercase.
- lpad(string, size, padstring) varchar ¶
Left pads
string
tosize
characters withpadstring
. Ifsize
is less than the length ofstring
, the result is truncated tosize
characters.size
must not be negative andpadstring
must be non-empty.
- ltrim(string) varchar ¶
Removes leading whitespace from string. See
trim()
for the set of recognized whitespace characters.
- ltrim(string, chars) varchar
Removes the longest substring containing only characters in
chars
from the beginning of thestring
.SELECT ltrim('test', 't'); -- est SELECT ltrim('tetris', 'te'); -- ris
- replace(string, search) varchar ¶
Removes all instances of
search
fromstring
.
- replace(string, search, replace) varchar
Replaces all instances of
search
withreplace
instring
.If
search
is an empty string, insertsreplace
in front of every character and at the end of thestring
.
- reverse(string) varchar
Reverses
string
.
- rpad(string, size, padstring) varchar ¶
Right pads
string
tosize
characters withpadstring
. Ifsize
is less than the length ofstring
, the result is truncated tosize
characters.size
must not be negative andpadstring
must be non-empty.
- rtrim(string) varchar ¶
Removes trailing whitespace from string. See
trim()
for the set of recognized whitespace characters.
- rtrim(string, chars) varchar
Removes the longest substring containing only characters in
chars
from the end of thestring
.SELECT rtrim('test', 't'); -- tes SELECT rtrim('test...', '.'); -- test
- split(string, delimiter)¶
Splits
string
ondelimiter
and returns an array.
- split(string, delimiter, limit)
Splits
string
ondelimiter
and returns an array of size at mostlimit
.The last element in the array always contains everything left in the string.
limit
must be a positive number.
- split_part(string, delimiter, index) string ¶
Splits
string
ondelimiter
and returns the part at indexindex
.Field indexes start with 1. If the index is larger than the number of fields, then null is returned.
- split_to_map(string, entryDelimiter, keyValueDelimiter) map<varchar, varchar> ¶
Splits
string
byentryDelimiter
andkeyValueDelimiter
and returns a map.entryDelimiter
splitsstring
into key-value pairs.keyValueDelimiter
splits each pair into key and value. Note thatentryDelimiter
andkeyValueDelimiter
are interpreted literally, i.e., as full string matches.entryDelimiter and keyValueDelimiter must not be empty and must not be the same.
Raises an error if there are duplicate keys.
- strpos(string, substring) bigint ¶
Returns the starting position of the first instance of
substring
instring
. Positions start with1
. If not found,0
is returned.
- strpos(string, substring, instance) bigint
Returns the position of the N-th
instance
ofsubstring
instring
.instance
must be a positive number. Positions start with1
. If not found,0
is returned. It takes into account overlapping strings when counting occurrences.SELECT strpos('aaa', 'aa', 2); -- 2
- strrpos(string, substring) bigint ¶
Returns the starting position of the last instance of
substring
instring
. Positions start with1
. If not found,0
is returned.
- strrpos(string, substring, instance) bigint
Returns the position of the N-th
instance
ofsubstring
instring
starting from the end of the string.instance
must be a positive number. Positions start with1
. If not found,0
is returned. It takes into account overlapping strings when counting occurrences.SELECT strrpos('aaa', 'aa', 2); -- 1
- substr(string, start) varchar ¶
Returns the rest of
string
from the starting positionstart
. Positions start with1
. A negative starting position is interpreted as being relative to the end of the string. Returns empty string if absolute value ofstart
is greater then length of thestring
.
- substr(string, start, length) varchar
Returns a substring from
string
of lengthlength
from the starting positionstart
. Positions start with1
. A negative starting position is interpreted as being relative to the end of the string. Returns empty string if absolute value of'start
is greater then length of thestring
.
- trim(string) varchar ¶
Removes starting and ending whitespaces from
string
.Recognized whitespace characters:
Code
Description
Code
Description
9
TAB (horizontal tab)
U+1680
Ogham Space Mark
10
LF (NL line feed, new line)
U+2000
En Quad
11
VT (vertical tab)
U+2001
Em Quad
12
FF (NP form feed, new page)
U+2002
En Space
13
CR (carriage return)
U+2003
Em Space
28
FS (file separator)
U+2004
Three-Per-Em Space
29
GS (group separator)
U+2005
Four-Per-Em Space
30
RS (record separator)
U+2006
Four-Per-Em Space
31
US (unit separator)
U+2008
Punctuation Space
32
Space
U+2009
Thin Space
_
_
U+200a
Hair Space
_
_
U+200a
Hair Space
_
_
U+2028
Line Separator
_
_
U+2029
Paragraph Separator
_
_
U+205f
Medium Mathematical Space
_
_
U+3000
Ideographic Space
- trim(string, chars) varchar
Removes the longest substring containing only characters in
chars
from the beginning and end of thestring
.SELECT trim('test', 't'); -- es SELECT trim('.t.e.s.t.', '.t'); -- e.s
- upper(string) varchar ¶
Converts
string
to uppercase.
Unicode Functions¶
- to_utf8(string) varbinary ¶
Encodes
string
into a UTF-8 varbinary representation.