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) = 'Ä'
CHR (NULL) = NULL

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 (E'1234', 'base64') = 'MTIzNA=='
DECODE ('MTIZAAE=', 'base64') = 123

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

LOWER ('TOM') = 'tom'
UPPER ('tom') = 'TOM'

MD5 ('abc') = '900150983cd24fb0d6963f7d28e17f72'

PG_CLIENT_ENCODING () = 'UTF8'

QUOTE_IDENT ('ABC DEF') = '"ABC DEF"'
QUOTE_IDENT ('"ABC"') = '"""ABC"""'

QUOTE_LITERAL (E'O\'Reilly') = ''O''Reilly''
QUOTE_LITERAL (42.5) = "'42.5'"
QUOTE_LITERAL ('"ABC"') = '''"ABC"'''

QUOTE_NULLABLE (NULL) = NULL
QUOTE_NULLABLE (42.5) = "'42.5'"

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
BIT_LENGTH ('JÖSE') = 40

Find a number of characters in a string.

CHAR_LENGTH ('jose') = 4
CHARACTER_LENGTH ('jose') = 4
LENGTH ('jose') = 4
LENGTH ('JÖSE', 'UTF8') = 4

OCTET_LENGTH ('AB') = 2
OCTET_LENGTH ('Ö') = 2
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.

SUBSTRING('ABCDE'from'%#"B_D#"_'for'#')='BCD'
SUBSTRING('ABCDE'from'%#"B-D#"_'for'#')=NULL

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.

REGEXP_MATCHES('1,2','(\d),(\d)')={'1','2'}
REGEXP_MATCHES('1,2','\d','g')={'1'},{'2'}

Split string using a POSIX regular expression as the delimiter.

REGEXP_SPLIT_TO_ARRAY ('ABC DEF', E'\\s+') = {ABC,DEF}
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