This page provides comprehensive PostgreSQL string functions that help you effectively manipulate text data.<\/p>\n\n\n\n
This section introduces the functions for extracting a substring from a string.<\/p>\n\n\n\n
n<\/code> characters of the string.<\/li>\n\n\n\n- RIGHT(string, n)<\/a> – Returns the last
n<\/code> characters of the string.<\/li>\n\n\n\n- SUBSTRING()<\/a> – Returns a substring from a string using various options.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 2. Case Manipulation Functions #<\/a><\/h2>\n\n\n\nThis section shows how to convert a string to lowercase, uppercase, and initial caps.<\/p>\n\n\n\n
\n- LOWER(string)<\/a> – Returns a new string with all characters in the input string converted to lowercase.<\/li>\n\n\n\n
- UPPER(string) <\/a> – Returns a new string with all characters in the input string converted to uppercase.<\/li>\n\n\n\n
- INITCAP(string)<\/a> – Return a new string with the letter of each word in the input string to uppercase and the remainder to lowercase.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 3. Finding Strings #<\/a><\/h2>\n\n\n\n\n- POSITION(substring IN string)<\/a> – Returns the starting location of the first instance of a substring in a string.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 4. Trimming Strings #<\/a><\/h2>\n\n\n\nThis section introduces various string trimming functions to remove specified characters from the start, end, or both ends of a string.<\/p>\n\n\n\n
\n- TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)<\/a> – Removes unwanted characters (or whitespace by default) from the beginning, end, or both ends of a string.<\/li>\n\n\n\n
- BTRIM(string[, characters])<\/a> – Removes the specified characters from both ends of a string.<\/li>\n\n\n\n
- LTRIM(string[, characters])<\/a> – Removes the specified characters from the start of a string.<\/li>\n\n\n\n
- RTRIM(string[, characters])<\/a> – Removes the specified characters from the end of a string.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 5. Formatting Strings #<\/a><\/h2>\n\n\n\nThis section shows you how to pad a string on the left or right with specified characters.<\/p>\n\n\n\n
\n- LPAD(string, length[, fill])<\/a> – Pads the string on the left with the specified characters until the result string has a certain length.<\/li>\n\n\n\n
- RPAD(string, length[, fill])<\/a> – Pads the string on the right with the specified characters until the result string has a certain length.<\/li>\n\n\n\n
- FORMAT(format, format_arg [, …])<\/a> – Returns a formatted string.<\/li>\n\n\n\n
- TO_CHAR(value, format)<\/a> – Formats a value such as a date, time, timestamp, number to a formatted string.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 6. Replacing, Repeating, and Overlaying Functions #<\/a><\/h2>\n\n\n\n\n- REPLACE(string, substring, new_substring)<\/a> – Replaces all occurrences of a substring in an input string with a new one.<\/li>\n\n\n\n
- TRANSLATE(string, from_set, to_set)<\/a> – Returns a string in which all characters in the
from_set<\/code> are replaced by the corresponding characters in the to_set<\/code> set.<\/li>\n\n\n\n- REPEAT(string, number)<\/a> – Repeats the given string a specified number of times.<\/li>\n\n\n\n
- OVERLAY(original PLACING replacement FROM start [FOR length])<\/a> – Replaces a substring specified by a starting position and length with a new substring.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 7. String Concatenation Functions #<\/a><\/h2>\n\n\n\n\n- CONCAT(s1, s2, …)<\/a> – Returns a string that is concatenated from multiple strings.<\/li>\n\n\n\n
- CONCAT_WS(separator, s1, s2, …)<\/a> – Returns a string that is the result of concatenating two or more strings with a separator.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 8. Regular Expression Functions #<\/a><\/h2>\n\n\n\n\n- REGEXP_MATCH(string, pattern [, flags])<\/a> – Returns an array of all substrings that match a regular expression pattern.<\/li>\n\n\n\n
- REGEXP_MATCHES(string, pattern [, flags])<\/a> – Returns a set of arrays of all substrings in a string, which matches a regular expression pattern.<\/li>\n\n\n\n
- REGEXP_REPLACE(string, pattern, replacement [, flags])<\/a> – Replaces substrings that match a regular expression pattern with a new string.<\/li>\n\n\n\n
- REGEXP_SPLIT_TO_ARRAY(string, pattern [, flags])<\/a> – Splits a string into an array of substrings using a POSIX regular expression as the delimiter.<\/li>\n\n\n\n
- REGEXP_SPLIT_TO_TABLE(string, pattern [, flags])<\/a> – Splits a string into multiple rows (a set of text values) based on a regular expression delimiter.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 9. Length and Character Functions #<\/a><\/h2>\n\n\n\n\n- LENGTH(string)<\/a> – Returns the number of characters in the string.<\/li>\n\n\n\n
- ASCII(string)<\/a> – Returns the ASCII code of the first character of the string.<\/li>\n\n\n\n
- CHR(integer)<\/a> – Returns the character corresponding to the given ASCII (or Unicode) code.<\/li>\n\n\n\n
- REVERSE(string)<\/a> – Returns a new string with the characters of the input string are in reverse order.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 10. Quoting Functions #<\/a><\/h2>\n\n\n\n\n- QUOTE_IDENT(string)<\/a> – Safely quote SQL identifiers.<\/li>\n\n\n\n
- QUOTE_LITERAL(string)<\/a> – Produces a safely escaped version of a literal string for inclusion in SQL commands.<\/li>\n\n\n\n
- QUOTE_NULLABLE(string)<\/a> – Similar to
QUOTE_LITERAL<\/code> but returns a ‘NULL’ string if the input is NULL<\/code>.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\t\n\t\tWas this tutorial helpful ?<\/div>\n\t\t\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t\n\t\t\n\t\t\t<\/div>\n\t\t\t\n\t\t\t