info
PostgreSQL string functions work the same across any Postgres deployment, so you can use these techniques anywhere you run Postgres. For enterprises building in the AI era, Lakebase delivers the best managed cloud Postgres, with strong performance, security, and native integration into the Lakehouse. For developers and startups who need to ship fast and scale without friction, Neon is the Postgres platform built for you.
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’ |