7.9. String Functions and Operators

• 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 theSQL-standard concatenation operator (||).

• hamming_distance(string1, string2) → bigint
• 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.

• length(string) → bigint
• Returns the length of string in characters.

• levenshtein_distance(string1, string2) → bigint
• 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.

• lower(string) → varchar
• Converts string to lowercase.

• lpad(string, size, padstring) → varchar
• Left pads string to size characters with padstring.If size is less than the length of string, the result istruncated to size characters. size must not be negativeand padstring must be non-empty.

• ltrim(string) → varchar
• Removes leading whitespace from string.

• replace(string, search) → varchar
• Removes all instances of search from string.

• replace(string, search, replace) → varchar

• reverse(string) → varchar
• Returns string with the characters in reverse order.

• rpad(string, size, padstring) → varchar
• Right pads string to size characters with padstring.If size is less than the length of string, the result istruncated to size characters. size must not be negativeand must be non-empty.

• rtrim(string) → varchar
• Removes trailing whitespace 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 mostlimit. The last element in the array always contain everythingleft 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 thanthe number of fields, then null is returned.

• split_to_map(string, entryDelimiter, keyValueDelimiter) → map<varchar, varchar>
• Splits string by entryDelimiter and keyValueDelimiter and returns a map.entryDelimiter splits string into key-value pairs. keyValueDelimiter splitseach pair into key and value. Note that entryDelimiter and keyValueDelimiter areinterpreted literally, i.e., as full string matches.

• split_to_map(string, entryDelimiter, keyValueDelimiter, function(K, V1, V2, R)) → map<varchar, varchar>
• Splits string by entryDelimiter and keyValueDelimiter and returns a map.entryDelimiter splits string into key-value pairs. keyValueDelimiter splitseach pair into key and value. Note that entryDelimiter and keyValueDelimiter areinterpreted literally, i.e., as full string matches. function(K,V1,V2,R)is invoked in cases of duplicate keys to resolve the value that should be in the map.

• split_to_multimap(string, entryDelimiter, keyValueDelimiter) -> map(varchar, array(varchar))
• Splits string by entryDelimiter and keyValueDelimiter and returns a mapcontaining an array of values for each unique key. entryDelimiter splits stringinto key-value pairs. keyValueDelimiter splits each pair into key and value. Thevalues for each key will be in the same order as they appeared in string.Note that entryDelimiter and keyValueDelimiter are interpreted literally,i.e., as full string matches.

• strpos(string, substring) → bigint
• Returns the starting position of the first instance of substring instring. Positions start with 1. If not found, 0 is returned.

• strpos(string, substring, instance) → bigint
• Returns the position of the N-th instance of substring in .instance must be a positive number.Positions start with 1. If not found, 0 is returned.

• strrpos(string, substring) → bigint
• Returns the starting position of the last instance of substring in string.Positions start with 1. If not found, 0 is returned.

• Returns the position of the N-th instance of substring in string starting from the end of the string.instance must be a positive number.Positions start with 1. If not found, 0 is returned.

• position(substring IN string) → bigint
• Returns the starting position of the first instance of substring instring. 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 interpretedas being relative to the end of the string.

• substr(string, start, length) → varchar
• Returns a substring from string of length length from the startingposition start. Positions start with 1. A negative startingposition is interpreted as being relative to the end of the string.

• trim(string) → varchar
• Removes leading and trailing whitespace from string.

• upper(string) → varchar
• Converts string to uppercase.

• word_stem(word) → varchar
• Returns the stem of word in the English language.

• word_stem(word, lang) → varchar
• Returns the stem of word in the lang language.

• normalize(string) → varchar
• Transforms string with NFC normalization form.

• normalize(string, form) → varchar
• Transforms string with the specified normalization form.form must be be one of the following keywords: Note This SQL-standard function has special syntax and requiresspecifying form as a keyword, not as a string.

• to_utf8(string) → varbinary
• Encodes string into a UTF-8 varbinary representation.

• from_utf8(binary) → varchar
• Decodes a UTF-8 encoded string from binary. Invalid UTF-8 sequencesare replaced with the Unicode replacement character U+FFFD.

• (binary, replace) → varchar