October 20, 2024
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’ |
| STRING_TO_ARRAY | Convert strings to arrays | STRING_TO_ARRAY(‘xx,yy,zz’, ‘,’) | {xx,yy,zz} |
| 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’ |