PostgreSQL String Functions

Section 1. Extracting Strings #

This section introduces the functions for extracting a substring from a string.

• LEFT(string, n) – Returns the first n characters of the string.
• RIGHT(string, n) – Returns the last n characters of the string.
• SUBSTRING() – Returns a substring from a string using various options.

Section 2. Case Manipulation Functions #

This section shows how to convert a string to lowercase, uppercase, and initial caps.

• LOWER(string) – Returns a new string with all characters in the input string converted to lowercase.
• UPPER(string) – Returns a new string with all characters in the input string converted to uppercase.
• INITCAP(string) – Return a new string with the letter of each word in the input string to uppercase and the remainder to lowercase.

Section 3. Finding Strings #

• POSITION(substring IN string) – Returns the starting location of the first instance of a substring in a string.

Section 4. Trimming Strings #

This section introduces various string trimming functions to remove specified characters from the start, end, or both ends of a string.

• TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) – Removes unwanted characters (or whitespace by default) from the beginning, end, or both ends of a string.
• BTRIM(string[, characters]) – Removes the specified characters from both ends of a string.
• LTRIM(string[, characters]) – Removes the specified characters from the start of a string.
• RTRIM(string[, characters]) – Removes the specified characters from the end of a string.

Section 5. Formatting Strings #

This section shows you how to pad a string on the left or right with specified characters.

• LPAD(string, length[, fill]) – Pads the string on the left with the specified characters until the result string has a certain length.
• RPAD(string, length[, fill]) – Pads the string on the right with the specified characters until the result string has a certain length.
• FORMAT(format, format_arg [, …]) – Returns a formatted string.

Section 6. Replacing, Repeating, and Overlaying Functions #

• REPLACE(string, substring, new_substring) – Replaces all occurrences of a substring in an input string with a new one.
• TRANSLATE(string, from_set, to_set) – Returns a string in which all characters in the from_set are replaced by the corresponding characters in the to_set set.
• REPEAT(string, number) – Repeats the given string a specified number of times.
• OVERLAY(original PLACING replacement FROM start [FOR length]) – Replaces a substring specified by a starting position and length with a new substring.

Section 7. String Concatenation Functions #

• CONCAT(s1, s2, …) – Returns a string that is concatenated from multiple strings.
• CONCAT_WS(separator, s1, s2, …) – Returns a string that is the result of concatenating two or more strings with a separator.

Section 8. Regular Expression Functions #

• REGEXP_MATCH(string, pattern [, flags]) – Returns an array of all substrings that match a regular expression pattern.
• REGEXP_MATCHES(string, pattern [, flags]) – Returns a set of arrays of all substrings in a string, which matches a regular expression pattern.
• REGEXP_REPLACE(string, pattern, replacement [, flags]) – Replaces substrings that match a regular expression pattern with a new string.
• REGEXP_SPLIT_TO_ARRAY(string, pattern [, flags]) – Splits a string into an array of substrings using a POSIX regular expression as the delimiter.
• REGEXP_SPLIT_TO_TABLE(string, pattern [, flags]) – Splits a string into multiple rows (a set of text values) based on a regular expression delimiter.

Section 9. Length and Character Functions #

• LENGTH(string) – Returns the number of characters in the string.
• ASCII(string) – Returns the ASCII code of the first character of the string.
• CHR(integer) – Returns the character corresponding to the given ASCII (or Unicode) code.
• REVERSE(string) – Returns a new string with the characters of the input string are in reverse order.

Section 10. Quoting Functions #

• QUOTE_IDENT(string) – Safely quote SQL identifiers.
• QUOTE_LITERAL(string) – Produces a safely escaped version of a literal string for inclusion in SQL commands.
• QUOTE_NULLABLE(string) – Similar to QUOTE_LITERAL but returns a ‘NULL’ string if the input is NULL.