Skip to content
aviral gupta
Get in touch

// B2.3 · ~30 min · Beginner

Expressions and string functions

After this lesson you can compute new columns from the ones a table has, reshape text, find rows by a pattern and give each row a label.

Lesson 3 of 5 in B2 Querying one table

You will be able to

  • Compute with + - * / % and round(), and cast with :: or CAST where integer division would cut off decimals
  • Build and change text with ||, upper, lower, length, substring, left, trim and replace
  • Match text with LIKE and ILIKE, and label rows with CASE WHEN

  1. Warm-up · Activity 1 of 7

    Warm-up: which operator joins two strings, as in first name and last name, in PostgreSQL?

  2. Predict · Activity 2 of 7

    Predict before you read on. What does this return?

    SELECT 7 / 2;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the query finds the titles that start with The.

    SELECT title FROM books WHERE title ____ 'The%';
    SELECT title FROM books WHERE title 'The%';

  4. Practice · Activity 4 of 7

    Match each expression to its result.

  5. Practice · Activity 5 of 7

    Put the parts of this CASE in order, so that Emma (1815) is labelled old and Dune (1965) modern.

    1. 1.ELSE 'recent'
    2. 2.CASE
    3. 3.WHEN year < 1900 THEN 'old'
    4. 4.WHEN year < 1980 THEN 'modern'
    5. 5.END AS era

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The titles are Dune, Emma, Kindred, Beloved, The Hobbit and Neuromancer. What does this return?

    SELECT count(*) FROM books
WHERE title LIKE '%e%';

  7. Apply · Activity 7 of 7

    Mini-task. For the sci-fi books, oldest first, write a query with two columns: label, such as Dune by Frank Herbert, and stock, which says several copies when a book has more than one copy and one copy otherwise. Then change the query to use ILIKE on the title and predict which books it finds.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Numbers, text and labels

setup.sql loads the library from module B1. main.sql computes with integers and a cast, builds a label from two columns, and gives each book an era with CASE, for the titles that contain a small e. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Arithmetic: integer division, remainder, a cast, rounding.
SELECT 7 / 2 AS int_div,
       7 % 2 AS remainder,
       7::numeric / 2 AS exact,
       round(7::numeric / 3, 2) AS rounded;

-- 2. Text: glue strings together and change them.
SELECT title || ' (' || year || ')' AS label,
       upper(genre) AS shelf,
       length(title) AS letters
FROM books
WHERE genre = 'sci-fi'
ORDER BY year;

-- 3. A pattern and a label per row.
SELECT title,
       CASE WHEN year < 1900 THEN 'old'
            WHEN year < 1980 THEN 'modern'
            ELSE 'recent'
       END AS era
FROM books
WHERE title LIKE '%e%'
ORDER BY year;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 int_div | remainder |       exact        | rounded
---------+-----------+--------------------+---------
       3 |         1 | 3.5000000000000000 |    2.33
(1 row)

       label        | shelf  | letters
--------------------+--------+---------
 Dune (1965)        | SCI-FI |       4
 Kindred (1979)     | SCI-FI |       7
 Neuromancer (1984) | SCI-FI |      11
(3 rows)

    title    |  era
-------------+--------
 The Hobbit  | modern
 Dune        | modern
 Kindred     | modern
 Neuromancer | recent
 Beloved     | recent
(5 rows)
  • 7 / 2 is 3: both are integers. After the cast, 7::numeric / 2 is exact.
  • year is an integer, but || turns it into text because the other side is text.
  • upper(genre) changes only the result; the table still holds sci-fi in lower case.
  • Emma is missing from the last result: LIKE '%e%' looks for a small e, and Emma has only a capital E.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Shelf codes

The library wants a shelf code for every book: the first three letters of the title in capitals, a hyphen, and the year, such as DUN-1965. Write a query with the columns title and code, ordered by id.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    left(title, 3) takes the first three characters.

  2. Hint 2

    Wrap it in upper(…) for capitals, then join the parts with ||.

  3. Hint 3

    upper(left(title, 3)) || '-' || year AS code

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT title,
       upper(left(title, 3)) || '-' || year AS code
FROM books
ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books and members.
SELECT title, year FROM books ORDER BY id;

test.sql

-- test: Dune gets the code DUN-1965
SELECT count(*) = 1 FROM books WHERE upper(left(title, 3)) || '-' || year = 'DUN-1965';

-- test: The query lists every book with its shelf code
-- output:
--     title    |   code
-- -------------+----------
--  Dune        | DUN-1965
--  Emma        | EMM-1815
--  Kindred     | KIN-1979
--  Beloved     | BEL-1987
--  The Hobbit  | THE-1937
--  Neuromancer | NEU-1984
-- (6 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Share of the copies

The library has 9 copies in all. The starter should show each book's share of them in percent, but every row says 0. Fix percent so it shows the share with one decimal place (Dune: 22.2). Then add a column stock that says several when a book has two or more copies and single otherwise. Keep the order by id.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    copies / 9 divides two integers: 2 / 9 is 0, and 0 * 100 stays 0.

  2. Hint 2

    Multiply by 100.0 first, which makes the calculation numeric, then round(…, 1).

  3. Hint 3

    CASE WHEN copies >= 2 THEN 'several' ELSE 'single' END AS stock

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Each book’s share of the 9 copies, in percent.
SELECT title,
       round(copies * 100.0 / 9, 1) AS percent,
       CASE WHEN copies >= 2 THEN 'several' ELSE 'single' END AS stock
FROM books
ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Each book’s share of the 9 copies, in percent.
SELECT title,
       copies / 9 * 100 AS percent
FROM books
ORDER BY id;

test.sql

-- test: The query lists each share with one decimal place and a stock label
-- output:
--     title    | percent |  stock
-- -------------+---------+---------
--  Dune        |    22.2 | several
--  Emma        |    11.1 | single
--  Kindred     |    11.1 | single
--  Beloved     |    11.1 | single
--  The Hobbit  |    33.3 | several
--  Neuromancer |    11.1 | single
-- (6 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

+ to join strings

SELECT title + ' (' + year + ')'
FROM books;

What psql prints

ERROR:  operator does not exist: text + unknown

Why, and the fix

In PostgreSQL, + adds numbers. For a text column and a quoted value (of type unknown until PostgreSQL decides) there is no + operator, and the message says so. Join strings with ||: title || ' (' || year || ')'.

Dividing by a value that can be zero

SELECT title, 100 / (copies - 1) AS per_extra_copy
FROM books;

What psql prints

ERROR:  division by zero

Why, and the fix

Emma has one copy, so copies - 1 is 0 for her row, and PostgreSQL stops the whole query instead of returning a row with no value. Keep those rows out with WHERE copies > 1, or handle them with CASE WHEN copies > 1 THEN 100 / (copies - 1) END.

CASE results of different types

SELECT title,
       CASE WHEN copies > 1 THEN 'many' ELSE copies END AS stock
FROM books;

What psql prints

ERROR:  invalid input syntax for type integer: "many"

Why, and the fix

All results of a CASE must have one type. copies is an integer, so PostgreSQL tries to read 'many' as an integer and fails. Make every branch text: ELSE copies::text, or better a word such as ELSE 'one'.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

Arithmetic, and the integer trap

The select list can compute: copies * 2, year + 100, 2026 - year. The operators are + - * / and %, the remainder: 7 % 2 is 1. Division of two integers gives an integer and cuts off the rest: 7 / 2 is 3, not 3.5. As soon as one side is numeric, the result is exact: 7 / 2.0 and 7::numeric / 2 both give 3.5000000000000000. :: is PostgreSQL’s cast; CAST(7 AS numeric) is the standard spelling of the same thing. round(x, 2) rounds a numeric to two places. A computed column has no name of its own, so psql shows ?column?; give it one with AS.

Text: glue it and change it

|| joins strings: title || ' (' || year || ')' gives Dune (1965). It turns a number into text for you, as long as one side is text. + does not join strings: on a text column it fails with operator does not exist: text + integer. The string functions return a new value and leave the table unchanged: upper('Dune') is DUNE, lower('Dune') is dune, length('Dune') is 4, left('Neuromancer', 5) is Neuro, substring('Neuromancer' from 5 for 3) is oma, trim(' Emma ') is Emma, and replace('sci-fi', '-', ' ') is sci fi. Functions can be nested: upper(left(title, 3)).

Patterns with LIKE, labels with CASE

title LIKE 'D%' is true for titles that start with D: % stands for any number of characters, _ for exactly one. The pattern must match the whole string, so LIKE 'Dun' matches only Dun itself; to find a word anywhere, write '%word%'. LIKE is case-sensitive, and ILIKE is the same without case: 'The Hobbit' ILIKE 'the%' is true. CASE WHEN year < 1900 THEN 'old' WHEN year < 1980 THEN 'modern' ELSE 'recent' END gives each row a label. The conditions are tried in order and the first true one wins, so put the narrowest first. All results must fit one type: mixing text and a number fails.

Sources

Last reviewed September 30, 2026