building menu...

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: String Functions and Operators

[Back to SQL]