String functions

PostgreSQL has dozens of functions for manipulating strings. In this lesson we look at the ones most common in real work: normalizing (LOWER/UPPER), measuring (LENGTH), composing (|| / CONCAT), extracting (SUBSTRING), cleaning up (TRIM), substituting (REPLACE), and pattern searching (LIKE / ILIKE).

The functions we will use

SQL

LOWER('Alice')                -- 'alice'
UPPER('alice')                -- 'ALICE'
LENGTH('alice')               -- 5
'a' || 'b'                    -- 'ab'  (concatenazione)
CONCAT('a', 'b', 'c')         -- 'abc' (versione "funzione")
SUBSTRING('hello' FROM 2 FOR 3) -- 'ell'
TRIM(' alice ')               -- 'alice'
REPLACE('foo bar', 'bar','x') -- 'foo x'

|| is the standard SQL operator for concatenating strings; CONCAT is more permissive (it ignores NULLs, while || propagates them: 'a' || NULL → NULL).

LIKE and ILIKE: searching by pattern

LIKE searches for a pattern: % = any sequence, _ = a single character. It is case-sensitive. ILIKE is the PostgreSQL case-insensitive variant:

SQL

WHERE name LIKE  '%Cuffie%'   -- match esatto "Cuffie"
WHERE name ILIKE '%cuffie%'   -- match anche "CUFFIE", "Cuffie", "cuffie"
WHERE email LIKE  '%@example.com'
WHERE first_name LIKE 'A_____'  -- inizia per A, lungo 6 (A + 5 caratteri)

To search for a literal % or _ inside the pattern, use ESCAPE:

SQL

WHERE titolo LIKE '%50\%%' ESCAPE '\';   -- contiene "50%"

Combining functions

Functions compose like in any language:

SQL

-- Email "normalizzata" e iniziali in maiuscolo:
SELECT LOWER(email) AS email_norm,
       UPPER(SUBSTRING(first_name FROM 1 FOR 1)) || '.'
       || UPPER(SUBSTRING(last_name  FROM 1 FOR 1)) || '.' AS iniziali
FROM   customers
ORDER BY id;

Try it

Exercise#sql.m6.l1.e1

Attempts: 0Loading…

For each customer, show the email in lowercase (column email) and the full name as 'first_name last_name' (column full_name). Order by id.

Loading editor…

Show hint

LOWER for the first column, || to concatenate with a space in between.

Solution available after 3 attempts

Review exercise

Exercise#sql.m6.l1.e2

Attempts: 0Loading…

Find all products that contain the word 'laptop' (in any case) in the name. Show only the name column. Order by name.

Loading editor…

Show hint

ILIKE is case-insensitive. Use '%laptop%' to match 'laptop' in any position.

Solution available after 3 attempts