PostgreSQL 18 reference · 31 entries

PostgreSQL String Functions

PostgreSQL string functions and operators: length(), substring(), position(), split_part(), concat(), format(), the || operator, ILIKE and regular expressions.

String positions start at 1. upper(), lower() and initcap() follow the database's locale, so accented letters work. Regular-expression functions use POSIX syntax; add the 'g' flag to act on every match.

Name	Description	Since
`LENGTH()`	Return the number of characters in a string. char_length() is the standard name; octet_length() counts bytes.
`LOWER()`	Convert a string to lowercase using the database's locale, so letters such as É and Ü work too.
`UPPER()`	Convert a string to uppercase using the database's locale, including accented letters such as é.
`INITCAP()`	Capitalize the first letter of each word and lowercase the rest, as in 'ada lovelace' to 'Ada Lovelace'.
`\|\|`	Join strings with the \|\| operator. If either side is NULL the result is NULL; use concat() to skip NULLs instead.
`CONCAT()`	Join any number of values into one string, treating NULL arguments as empty strings.	9.1
`CONCAT_WS()`	Join values with a separator between them, skipping NULLs. The first argument is the separator.	9.1
`FORMAT()`	Build a string from a template: %s inserts text, %I a quoted identifier and %L a quoted literal, for safe dynamic SQL.	9.1
`SUBSTRING()`	Extract part of a string by 1-based position and length, or the part matching a regular expression.
`LEFT()`	Return the first n characters of a string. A negative n returns all but the last \|n\| characters.	9.1
`RIGHT()`	Return the last n characters of a string. A negative n returns all but the first \|n\| characters.	9.1
`POSITION()`	Return the 1-based position of a substring inside a string, or 0 if it isn't found. strpos() takes the arguments in the other order.
`REPLACE()`	Replace every occurrence of a substring with another string. The search is case-sensitive.
`TRANSLATE()`	Replace single characters by position: each character in 'from' becomes the matching character in 'to', or is deleted.
`TRIM()`	Remove spaces, or characters you list, from the start, end or both ends of a string. btrim() is the function form.
`LTRIM()`	Remove spaces, or characters you list, from the start of a string only.
`RTRIM()`	Remove spaces, or any characters you list, from the end (right side) of a string only.
`LPAD()`	Pad a string on the left to a given length with spaces or another string. Longer strings are cut to that length.
`RPAD()`	Pad a string on the right to a given length with spaces or another string. Longer strings are cut to that length.
`REPEAT()`	Repeat a string a given number of times. A count of 0 or less gives an empty string.
`REVERSE()`	Return a string with its characters in reverse order. Since PostgreSQL 18 it also reverses bytea values.	9.1
`SPLIT_PART()`	Split a string on a delimiter and return the nth field (1-based). Negative n counts from the end.
`STRING_TO_ARRAY()`	Split a string on a delimiter into a text array. A third argument turns matching fields into NULL.
`STARTS_WITH()`	Return true if a string starts with a given prefix. The ^@ operator does the same and can use an index.	11
`MD5()`	Return the MD5 hash of a string as 32 hex characters. Fine for checksums, never for passwords.
`ILIKE`	Match a string against a LIKE pattern ignoring case: % matches any text and _ one character. ~~* is the operator form.
`REGEXP_REPLACE()`	Replace text matching a POSIX regular expression. Add the 'g' flag to replace every match, not just the first.
`REGEXP_MATCH()`	Return the captured groups of the first regular-expression match as a text array, or NULL if there's no match.	10
`REGEXP_SPLIT_TO_TABLE()`	Split a string on a regular expression and return one row per piece. regexp_split_to_array() returns an array.
`REGEXP_COUNT()`	Count how many times a regular expression matches in a string. Added in PostgreSQL 15.	15
`CHR()`	Return the character for a Unicode code point, such as chr(65) for 'A'. ascii() does the reverse.