PostgreSQL Tutorial: String Functions

June 30, 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’
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’