PostgreSQL: String Functions and Operators

string || string

text

String concatenation

string || non-string

text

String concatenation with one non-string input

char_length(string)

int

Number of characters in string

lower(string)

text

Convert string to lower case

overlay(string placing string from int [for int])

text

Replace substring

position(substring in string)

int

Location of specified substring

substring(string [from int] [for int])

text

Extract substring

substring(string from pattern)

text

Extract substring from POSIX regular expression

upper(string)

text

Convert string to upper case

trim([leading | trailing | both] [characters] from string)

text

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

initcap(string)

text

Convert the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters

regexp_matches(string text, pattern text, replacement text, [, flags text])

setof text[]

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

regexp_replace(string text, pattern text, replacement text [, flags text])

text

Replace substring(s) matching a POSIX regular expression

regexp_split_to_array(string text, pattern text [, flags text])

text[]

Split string using a POSIX regular expression as the delimeter

regexp_split_to_table(string text, pattern text [, flags text])

setof text

Split string using a POSIX regular expression as the delimiter

replace(string text, from text, to text)

text

Replace all occurrences in string of substring from with substring to