SQL: PostgreSQL String Functions

1. 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, ', ', ' ');

2. References

• PostgreSQL: String Functions and Operators