September 26, 2023
This page provides you with the most commonly used PostgreSQL string functions that allow you to manipulate string data effectively.
Function | Description | Example | Result |
---|---|---|---|
ASCII | Return the ASCII code value of a character or Unicode code point of a UTF8 character | ASCII(‘A’) | 65 |
CHR | Convert an ASCII code to a character or a Unicode code point to a UTF8 character | CHR(65) | ‘A’ |
CONCAT | Concatenate two or more strings into one | CONCAT(‘A’, ’B’, ’C’) | ‘ABC’ |
CONCAT_WS | Concatenate strings with a separator | CONCAT_WS(‘,’, ’A’, ’B’, ’C’) | ‘A,B,C’ |
FORMAT | Format arguments based on a format string | FORMAT(‘Hello %s’,’PostgreSQL’) | ‘Hello PostgreSQL’ |
INITCAP | Convert words in a string to title case | INITCAP(‘hI tHERE’) | Hi There |
LEFT | Return the first n character in a string | LEFT(‘ABC’,1) | ‘A’ |
LENGTH | Return the number of characters in a string | LENGTH(‘ABC’) | 3 |
LOWER | Convert a string to lowercase | LOWER(‘hI tHERE’) | ‘hi there’ |
LPAD | Pad on the left a a string with a character to a certain length | LPAD(‘123′, 5, ’00’) | ‘00123’ |
LTRIM | Remove the longest string that contains specified characters from the left of the input string | LTRIM(‘00123’) | ‘123’ |
MD5 | Return MD5 hash of a string in hexadecimal | MD5(‘ABC’) | |
POSITION | Return the location of a substring in a string | POSTION(‘B’ in ‘A B C’) | 3 |
REGEXP_MATCHES | Match a POSIX regular expression against a string and returns the matching substrings | SELECT REGEXP_MATCHES(‘ABC’, ‘^(A)(..)$’, ‘g’); | {A,BC} |
REGEXP_REPLACE | Replace substrings that match a POSIX regular expression by a new substring | REGEXP_REPLACE(‘John Doe’, ‘(.*) (.*)’, ’\2, \1′); | ‘Doe, John’ |
REPEAT | Repeat string the specified number of times | REPEAT(‘*’, 5) | ‘*****’ |
REPLACE | Replace all occurrences in a string of substring from with substring to | REPLACE(‘ABC’, ’B’, ’A’) | ‘AAC’ |
REVERSE | Return reversed string. | REVERSE(‘ABC’) | ‘CBA’ |
RIGHT | Return last n characters in the string. When n is negative, return all but first |n| characters. | RIGHT(‘ABC’, 2) | ‘BC’ |
RPAD | Pad on the right of a string with a character to a certain length | RPAD(‘ABC’, 6, ‘xo’) | ‘ABCxox’ |
RTRIM | Remove the longest string that contains specified characters from the right of the input string | RTRIM(‘abcxxzx’, ‘xyz’) | ‘abc’ |
SPLIT_PART | Split a string on a specified delimiter and return nth substring | SPLIT_PART(‘2017-12-31′, ’-‘, 2) | ’12’ |
SUBSTRING | Extract a substring from a string | SUBSTRING(‘ABC’, 1, 1) | ‘A’ |
TO_CHAR | Return a string in TEXT data type that represents the first argument formatted according to the specified format |
TO_CHAR(TIMESTAMP ‘2017-08-18 22:30:59’, ‘HH24:MI:SS’) | ‘22:30:59’ |
TO_NUMBER | converts a character string to a numeric value | TO_NUMBER(‘12,345.6-’, ‘99G999D9S’) | -12345.6 |
TRANSLATE | Return a string with the characters in the one set of characters are replaced by another set of characters | TRANSLATE(‘12345’, ‘143’, ‘ax’) | ‘a2x5’ |
TRIM | Remove the longest string that contains specified characters from the left, right or both of the input string | TRIM(’ ABC ‘) | ‘ABC’ |
UPPER | Convert a string to uppercase | UPPER(‘hI tHERE’) | ‘HI THERE’ |