Working with Text
PostgreSQL has many functions for working with text. Here are the most common ones.
| Function | What it does | Example | Result |
|---|---|---|---|
UPPER(text) | Capital letters | UPPER('Alice') | ALICE |
LOWER(text) | Small letters | LOWER('Alice') | alice |
LENGTH(text) | Number of characters | LENGTH('Alice') | 5 |
LEFT(text, n) | First n characters | LEFT('Alice', 2) | Al |
REPLACE(text, a, b) | Replace a with b | REPLACE('IT', 'I', 'A') | AT |
Example 1: Change the Case
SELECT UPPER(name) AS upper_name,
LOWER(department) AS dept
FROM employees
WHERE age > 31;
Output:
upper_name | dept
------------+---------
BOB | it
JACK | finance
PAUL | finance
THOMAS | it
(4 rows)
The functions change only what is shown. The data in the table stays the same.
Example 2: Join Text with ||
The || operator joins pieces of text together. This is called concatenation:
SELECT name || ' works in ' || department AS info
FROM employees
WHERE department = 'HR';
Output:
info
-------------------
Alice works in HR
Anna works in HR
(2 rows)
' works in ' is plain text, so it goes in single quotes. Notice the spaces inside the quotes. Without them, the words would run together.
Example 3: Use a Function in WHERE
Functions also work in WHERE:
SELECT name, LENGTH(name) AS letters
FROM employees
WHERE LENGTH(name) > 5;
Output:
name | letters
--------+---------
Olivia | 6
Thomas | 6
(2 rows)
This returns the employees whose name has more than 5 characters.
Tip
If any piece in a || chain is NULL, the whole result is NULL. The function CONCAT(a, b, ...) skips NULL pieces instead.
In the exercise, build a label from a name and a department.