9.6. String Functions and Operators
String Operators
The || operator performs concatenation.
String Functions
Warning
Currently, all of the string functions work incorrectly for Unicode (non-ASCII) strings. They operate as if strings are a sequence of UTF-8 bytes rather than a sequence of Unicode characters. For example, length() returns the number of bytes in the UTF-8 representation of the string rather than the number of unicode characters.
- chr(n) → varchar
Returns the Unicode code point n as a single character string.
- concat(string1, string2) → varchar
Returns the concatenation of string1 and string2. This function provides the same functionality as the SQL-standard concatenation operator (||).
- length(string) → bigint
Returns the length of string in characters.
- lower(string) → varchar
Converts string to lowercase.
- ltrim(string) → varchar
Removes leading spaces from string.
- replace(string, search) → varchar
Removes all instances of search from string.
- replace(string, search, replace) → varchar
Replaces all instances of search with replace in string.
- reverse(string) → varchar
Returns string with the characters in reverse order.
- rtrim(string) → varchar
Removes trailing spaces from string.
- split(string, delimiter) → array<varchar>
Splits string on delimiter and returns an array.
- split(string, delimiter, limit) → array<varchar>
Splits string on delimiter and returns an array of size at most limit. The last element in the array always contain everything left in the string. limit must be a positive number.
- split_part(string, delimiter, index) → varchar
Splits string on delimiter and returns the field index. Field indexes start with 1. If the index is larger than than the number of fields, then null is returned.
- strpos(string, substring) → bigint
Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.
- substr(string, start) → varchar
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.
- substr(string, start, length) → varchar
Returns a substring from string of length length from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of the string.
- trim(string) → varchar
Removes leading and trailing spaces from string.
- upper(string) → varchar
Converts string to uppercase.