SQL: PostgreSQL String Functions
This page presents a series of example commands that hopefully illustrate the use of various PostgreSQL string functions. A link to the official documentation can be found at the bottom of the page.
String Functions
String concatenation
The concatenation operator || can be used in SELECTs:
SELECT firstname || ' ' || lastname AS fullname FROM members ORDER BY lastname, firstname;
and in other SQL commands:
UPDATE members SET newphone = '(' || prefix || ') ' || phone WHERE prefix != '';
UPDATE members SET newphone = '(02) ' || phone WHERE state = 'NSW';
Number of characters in string
The following are equivalent:
SELECT lastname FROM members WHERE length(lastname) > 8;
SELECT lastname FROM members WHERE char_length(lastname) > 8;
SELECT lastname FROM members WHERE character_length(lastname) > 8;
There are also similar functions for determining the bit_length and octet_length of a string.
Upper- and Lowercase
SELECT * FROM members WHERE upper(username) = 'CHIRP';
SELECT * FROM members WHERE lower(username) = 'chirp';
This is also useful when sorting when you want to ignore the case:
SELECT firstname, lastname FROM members ORDER BY upper(lastname);
Trim
This function can be used to remove unwanted spaces from a field or string:
UPDATE members SET firstname = trim(both from firstname), lastname = trim(both from lastname);
or to remove other characters:
UPDATE members SET address = trim(both '"' from address);
In place of both you can also use leading or trailing. The character to be replaced is a space by default.
Substring Replace
The following command will replace ', ' with ' ' in the address field:
UPDATE members SET address = replace(address, ', ', ' ');
References
- PostgreSQL: PostgreSQL: String Functions and Operators