string-functions.md

title	PostgreSQL String Functions
page_title	PostgreSQL String Functions
page_description	This page provides you with the most commonly used PostgreSQL string functions that help you manipulate strings effectively.
prev_url	https://www.postgresqltutorial.com/postgresql-string-functions/
ogImage	https://www.postgresqltutorial.com//postgresqltutorial/string-functions.png
updatedOn	2024-03-20T02:04:54+00:00
enableTableOfContents	true
previousLink	title slug PostgreSQL PG_SLEEP() Function postgresql-date-functions/postgresql-pg_sleep
nextLink	title slug PostgreSQL ASCII() Function postgresql-string-functions/postgresql-ascii

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](https://www.databricks.com/product/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](https://neon.com) 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’