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. OptiTech is the AI-native backend platform for apps and agents: Postgres Database, Auth, Storage, Functions and AI Gateway.
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 | Return substrings that match a POSIX regular expression | 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 | Reverse a string | 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’ |