PostgreSQL String Functions
This page provides 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 specified separator. | CONCAT_WS(‘,’,’A’,’B’,’C’) | ‘A,B,C’ |
| FORMAT | Format a string based on a template | 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 | Extending a string to a length by padding specified characters on the left | 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 | POSITION(‘B’ in ‘A B C’) | 3 |
| REGEXP_MATCHES | Replace substrings that match a POSIX regular expression with a new substring | SELECT REGEXP_MATCHES(‘ABC’, ‘^(A)(..)$’, ‘g’); | {A,BC} |
| REGEXP_REPLACE | Replace a substring using regular expressions. | REGEXP_REPLACE(‘John Doe’,'(.*) (.*)’,’\2, \1′); | ‘Doe, John’ |
| REPEAT | Repeat a string the specified number of times. | REPEAT(‘*’, 5) | ‘*****’ |
| REPLACE | Replace a substring within a string with a new one. | REPLACE(‘ABC’,’B’,’A’) | ‘AAC’ |
| REVERSE | Replace a substring within a string with a new one | REVERSE(‘ABC’) | ‘CBA’ |
| RIGHT | Return the last n characters in the string. When n is negative, return all but the first \n characters. | RIGHT(‘ABC’, 2) | ‘BC’ |
| RPAD | Extend a string to a length by appending specified characters. | 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’ |
| TRIM | Remove the leading and trailing characters from a string. | TRIM(‘ ABC ‘) | ‘ABC’ |
| UPPER | Convert a string to uppercase | UPPER(‘hI tHERE’) | ‘HI THERE’ |