Postgres String Functions

Here we are going to give you a short description of PostgreSQL string functions. If you prefer to have everything on one page, download our free PostgreSQL String Functions – Cheat Sheet.

Postgres String  Functions

Here we want to highlight four main types of Postgres string functions: Conversion, Measurement, Modification, and Search & Matching.

Postgres String Functions: Conversion

ASCII – code of the first byte of the argument.

ASCII ('x') = 120
ASCII ('Ä') = 1234

 Convert string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings).

TO_ASCII ('Karel') = 'Karel'

A character with the given code.

CHR (64) = 'A'
CHR (1234) = 'Ä'

Convert string to dest_encoding.

CONVERT ('TEXT', 'UTF8', 'LATIN1') = 'Text'

Convert string to the database encoding / dest_encoding.

CONVERT_FROM ('TEXT', 'UTF8') = 'Text'
CONVERT_TO ('Text', 'UTF8') = 'Text'

Encode / Decode binary data into or from textual representation in a string.

ENCODE (E'1234', 'base64') = 'MTIzNA=='
DECODE ('MTIZAAE=', 'base64') = 123

Convert the first letter of each word to upper case and the rest to lower case.

INITCAP ('hi thomas') = 'Hi Thomas'
INITCAP ('all in all') = 'All In All'
INITCAP ('all_in_all') = 'All In All'
INITCAP ('go2bed') = 'Go2bed'

Convert string to lower or upper case.

LOWER ('TOM') = 'tom'
UPPER ('tom') = 'TOM'
Calculates the MD5 hash of a string, returning the result in hexadecimal.
MD5 ('abc') = '900150983cd24fb0d6963f7d28e17f72'
Current client encoding name.
Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary. Embedded quotes are properly doubled.
QUOTE_IDENT ('"ABC"') = '"""ABC"""'
Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single quotes and backslashes are properly doubled.
QUOTE_LITERAL (E'O\'Reilly') = ''O''Reilly''
QUOTE_LITERAL (42.5) = "'42.5'"
QUOTE_LITERAL ('"ABC"') = '''"ABC"'''
Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, return NULL.
QUOTE_NULLABLE (42.5) = "'42.5'"
Convert number to its equivalent hexadecimal representation.
TO_HEX (12) = 'c'
TO_HEX (42) = '2a'
TO_HEX (023150) = '5a6e'
TO_HEX (2147483647) = '7fffffff'

Postgres String Functions: Measurement

Find a number of bits in a string.

BIT_LENGTH ('J') = 8
BIT_LENGTH ('Ö') = 16
BIT_LENGTH ('jose') = 32

Find a number of characters in a string.

CHAR_LENGTH ('jose') = 4
LENGTH ('jose') = 4
LENGTH ('JÖSE', 'UTF8') = 4
Find a number of bytes in a string.
OCTET_LENGTH ('?') = 4

 Postgres String Functions: Modification

String concatenation.

'Postgre' || 'SQL' = 'PostgreSQL'
'Value: ' || 42 = 'Value: 42'

Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string.

BTRIM (' AB ') = 'AB'
TRIM (' AB ') = 'AB'
BTRIM ('=-AB-=',  '-=') = 'AB'
TRIM (both '-=' from '=-AB-=') = 'AB'
LTRIM ('=-AB-=', '-=') = 'AB-='
TRIM (leading '-=' from '=-AB-=') = 'AB-='
RTRIM ('=-AB-=', '-=') = '=-AB'
TRIM (trailing '-=' from '=-AB-=') = '=-AB'

Fill up the string to length by prepending/appending the characters fill (space by default).

LPAD ('ABC', 6) = ' ABC'
RPAD ('ABC', 6) = 'ABC '
LPAD ('123', 6, '0') = '000123'
RPAD ('C', 3, '+') = 'C++'

Replace substring.

OVERLAY('123' placing '-' from 2 for 3)='1-'
OVERLAY('123' placing '-' from 1 for 2)='-3'
OVERLAY('123' placing '-' from 2 for 1)='1-3'

Repeat string the specified number of times.

REPEAT ('Pg', 4) = 'PgPgPgPg'

Any character in a string that matches a character in the form set is replaced by the corresponding character in the to set.

TRANSLATE ('12321', '12', 'ab') = 'ab3ba'

Replace substring(s) matching a POSIX regular expression.

REGEXP_REPLACE('Pipe','p','-') = 'Pi-e'
REGEXP_REPLACE('Pipe','p','-','i') = '-ipe'
REGEXP_REPLACE('Pipe','p','-','g') = 'Pi-e'
REGEXP_REPLACE('Pipe','p','-','gi') = '-i-e'
REGEXP_REPLACE('24 h','\d+','00') = '00 h'
REGEXP_REPLACE('24 h','\s','_') = '24_h'
REGEXP_REPLACE('24 h','[\sh]+','?') = '24?'
REGEXP_REPLACE('\abc','\\.+','*') = '*'

Split string on delimiter and return the given field (counting from one).

SPLIT_PART ('1,2,3', ',', 2) = '2'

Extract substring.

SUBSTRING ('12345' from 2 for 3) = '234'
SUBSTR ('12345', 3, 2) = '34'

Extract substring matching POSIX regular expression.

SUBSTRING ('Regex' from '.{3}$') = 'gex'

Extract substring matching SQL regular expression.


Replace all occurrences of one substring with another substring.

REPLACE ('A-B-C', '-','+') = 'A+B+C'

 Postgres String Functions: Search & Matching

Location of the specified substring.

POSITION ('om' in 'Thomas') = 3
STRPOS ('Thomas', 'om') = 3

Return all captured substrings resulting from matching a POSIX regular expression against the string.


Split string using a POSIX regular expression as the delimiter.

REGEXP_SPLIT_TO_TABLE ('ABC DEF', E'\\s+') = 'ABC' 'DEF' (2 rows)

Return true if the string matches the supplied pattern.

'ABC' LIKE '_B_' = true 
'ABC' NOT LIKE '_B_' = false 
'ABC' SIMILAR TO '[ABC]+' = true 
'ABC' NOT SIMILAR TO '[ABC]+' = false

2 thoughts on “Postgres String Functions

Leave a Reply

Your email address will not be published. Required fields are marked *