Working with Text

PostgreSQL has many functions for working with text. Here are the most common ones.

FunctionWhat it doesExampleResult
UPPER(text)Capital lettersUPPER('Alice')ALICE
LOWER(text)Small lettersLOWER('Alice')alice
LENGTH(text)Number of charactersLENGTH('Alice')5
LEFT(text, n)First n charactersLEFT('Alice', 2)Al
REPLACE(text, a, b)Replace a with bREPLACE('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.