String functions and operators

Devart ODBC Driver for PostgreSQL supports the following SQL string functions and operators for working with text values.
They cover common tasks such as concatenation, trimming, padding, measuring length, substring extraction, pattern matching, and Unicode-aware processing.
Some functions (for example, Unicode normalization and casefold) are available only when the server encoding is UTF8.

SQL string functions and operators

Function/Operator	Return type	Description
text || text	text	Concatenates two text values.
text || anynonarray anynonarray || text	text	Converts the non-text value to text and concatenates the two values. The non-text value cannot be an array; cast arrays to text explicitly before concatenation.
btrim(string text [, characters text])	text	Removes the longest leading and trailing substring consisting only of characters from characters (space by default).
text IS [NOT] [form] NORMALIZED	boolean	Checks whether the string is (or is not) in the specified Unicode normalization form (NFC by default; other forms: NFD, NFKC, NFKD). Available only when the server encoding is UTF8.
bit_length(text)	integer	Returns the number of bits in the string (8 × octet_length).
char_length(text) character_length(text)	integer	Returns the number of characters in the string.
lower(text)	text	Converts the string to lowercase according to the database locale.
lpad(string text, length integer [, fill text])	text	Pads the string on the left with fill (space by default) to the specified length; truncates on the right if the string is longer than length.
ltrim(string text [, characters text])	text	Removes the longest leading substring consisting only of characters from characters (space by default).
normalize(text [, form])	text	Converts the string to the specified Unicode normalization form (NFC by default). Available only when the server encoding is UTF8.
octet_length(text)	integer	Returns the number of bytes in the string.
octet_length(character)	integer	Returns the number of bytes in the value of type character without stripping trailing spaces.
overlay(string text PLACING newsubstring text FROM start integer [FOR count integer])	text	Replaces the substring of string starting at position start and spanning count characters with newsubstring.
position(substring text IN string text)	integer	Returns the first starting index of substring within string, or zero if substring is not found.
rpad(string text, length integer [, fill text])	text	Pads the string on the right with fill (space by default) to the specified length; truncates if the string is longer than length.
rtrim(string text [, characters text])	text	Removes the longest trailing substring consisting only of characters from characters (space by default).
substring(string text [FROM start integer] [FOR count integer])	text	Returns the substring of string starting at position start and extending for count characters; start or count can be omitted.
substring(string text FROM pattern text)	text	Returns the first substring of string that matches the specified POSIX regular expression pattern.
substring(string text SIMILAR pattern text ESCAPE escape text) substring(string text FROM pattern text FOR escape text)	text	Returns the first substring of string that matches the specified SQL regular expression pattern. The second form is obsolete.
trim([LEADING\|TRAILING\|BOTH] [characters text] FROM string text)	text	Removes the longest leading, trailing, or both leading and trailing substrings consisting only of characters from characters (space by default). BOTH is the default.
trim([LEADING\|TRAILING\|BOTH] [FROM] string text [, characters text])	text	Non-standard syntax for trim() that provides equivalent functionality with a different argument order.
unicode_assigned(text)	boolean	Returns true if all characters in the string have assigned Unicode code points; otherwise returns false. Available only when the server encoding is UTF8.
upper(text)	text	Converts the string to uppercase according to the database locale.

Other string functions and operators

Function/Operator	Return type	Description
text ^@ text	boolean	Returns true if the first string starts with the second string (equivalent to starts_with).
ascii(text)	integer	Returns the numeric code point of the first character of the string.
casefold(text)	text	Performs case folding for case-insensitive comparisons (UTF8 only, often similar to lower).
chr(integer)	text	Returns the character with the specified code point.
concat(val1 "any" [, ...])	text	Concatenates the text representation of all arguments, ignoring NULL values.
concat_ws(sep text, val1 "any" [, ...])	text	Concatenates the text representation of all arguments using sep as a separator; ignores NULL values.
format(formatstr text [, ...])	text	Formats the arguments according to the given format string, similar to C sprintf.
initcap(text)	text	Converts the first letter of each word to uppercase and the remaining letters to lowercase.
left(string text, n integer)	text	Returns the first n characters of the string; if n is negative, returns all but the last \|n\| characters.
length(text)	integer	Returns the number of characters in the string.
md5(text)	text	Returns the MD5 hash of the argument as a hexadecimal string.
parse_ident(qualified_identifier text [, strict_mode boolean])	text[]	Splits a possibly qualified SQL identifier into an array of components, removing quoting; strict_mode controls error handling.
pg_client_encoding()	name	Returns the current client encoding name.
quote_ident(text)	text	Returns a string suitably quoted for use as an SQL identifier, quoting only when necessary.
quote_literal(text) quote_literal(anyelement)	text	Returns a string suitably quoted for use as an SQL string literal; returns NULL on NULL input.
quote_nullable(text) quote_nullable(anyelement)	text	Returns a string suitably quoted as an SQL literal; returns the literal NULL if the input is NULL.
regexp_count(string, pattern [, start [, flags]])	integer	Returns the number of times the POSIX regular expression pattern matches within string, starting at the optional position start.
regexp_instr(string, pattern [, start [, N [, endoption [, flags [, subexpr]]]]])	integer	Returns the position of the n-th match of the POSIX regular expression pattern in string, or 0 if no match is found.
regexp_like(string text, pattern text [, flags text])	boolean	Returns true if the POSIX regular expression pattern matches anywhere in string.
regexp_match(string text, pattern text [, flags text])	text[]	Returns an array of substrings captured by the first match of the POSIX regular expression pattern.
regexp_matches(string text, pattern text [, flags text])	setof text[]	Returns a set of arrays of substrings captured by matches of the POSIX regular expression pattern; with the g flag, returns all matches.
regexp_replace(string, pattern, replacement [, flags])	text	Replaces occurrences of the POSIX regular expression pattern in string with replacement; flags control whether all or only the first match is replaced.
regexp_replace(string, pattern, replacement, start [, N [, flags]])	text	Replaces the N-th match of pattern in string starting at position start with replacement.
regexp_split_to_array(string, pattern [, flags])	text[]	Splits string into an array of fields using the POSIX regular expression pattern as the delimiter.
regexp_split_to_table(string, pattern [, flags])	setof text	Splits string into a set of rows using the POSIX regular expression pattern as the delimiter.
regexp_substr(string, pattern [, start [, N [, flags [, subexpr]]]])	text	Returns the substring corresponding to the N-th match of the POSIX regular expression pattern in string.
repeat(string text, number integer)	text	Returns a new string consisting of string repeated number times.
replace(string text, from text, to text)	text	Replaces all occurrences of the substring from with to in string.
reverse(text)	text	Returns the string with characters in reverse order.
right(string text, n integer)	text	Returns the last n characters of the string; if n is negative, returns all but the first *	n	* characters.
split_part(string text, delimiter text, n integer)	text	Splits string on delimiter and returns the N-th field (1-based).
starts_with(string text, prefix text)	boolean	Returns true if string starts with prefix.
string_to_array(string, delimiter [, null_string])	text[]	Splits string into a text array using delimiter; fields equal to null_string are returned as NULL.
string_to_table(string, delimiter [, null_string])	setof text	Splits string into a set of rows using delimiter; fields equal to null_string are returned as NULL.
strpos(string text, substring text)	integer	Returns the starting index of substring within string; argument order is reversed compared to position.
substr(string text, start integer [, count integer])	text	Returns a substring of string starting at start for count characters; if count is omitted, returns the rest of the string.
to_ascii(string text [, encoding])	text	Converts string to ASCII, typically by transliterating or dropping non-ASCII characters.
to_bin(integer \| bigint)	text	Returns the two’s complement binary representation of the numeric value.
to_hex(integer \| bigint)	text	Returns the two’s complement hexadecimal representation of the numeric value.
to_oct(integer \| bigint)	text	Returns the two’s complement octal representation of the numeric value.
translate(string text, from text, to text)	text	Replaces each character in string that appears in from with the corresponding character in to. Extra characters in from without a counterpart in to are removed.
unistr(text)	text	Interprets escape sequences such as \XXXX or \uXXXX as Unicode code points and returns the resulting text.