◀ Course contents Part 2 · Module 2-05

Text & Number Functions

Reshaping values on the way out — and the division that returns zero

The last four modules were about which rows and how many. This one is about the values themselves: cutting text up, gluing it together, and rounding numbers. It ends on the single most expensive piece of arithmetic in SQL, a percentage that comes back as 0 without an error anywhere.

Ready?

Cutting, Measuring and Cleaning

These functions compute a new value for the result set. They never change what is stored — the same way an arithmetic expression in module 1-04 never changed a price. A SELECT reads.

`UPPER` / `LOWER`

Change the case. Mostly useful for comparing two values that were typed by different people.

`LENGTH(text)`

How many characters. A quick way to find values that are truncated or padded.

✂

`substr(text, start, len)`

A slice. Positions start at 1, and omitting the length runs to the end.

`strpos(text, needle)`

The position of the first occurrence, or 0 if it is not in there. Postgres also spells it position(needle IN text).

↹

`TRIM` / `REPLACE`

Strip surrounding whitespace; swap every occurrence of a substring.

substr and strpos are usually used together, because the interesting cut is at a character you have to find first:

SELECT name,
       substr(name, 1, strpos(name, ' ') - 1) AS first_name
FROM users;

strpos returns the position of the space, so the - 1 is what stops the space itself coming along. Get that wrong and every value has an invisible trailing space, which then fails to match anything for reasons nobody can see.

Clean on the way out, or clean the data?

TRIM in a query fixes the report you are writing. It does not fix the next report, or anyone else's. If a column really is full of padded values, the durable fix is an UPDATE — module 4-04 — and a constraint that stops it happening again. Wrapping every query in TRIM forever is a symptom that the cleaning never happened.

Quick check

substr('Product', 1, 4) returns what?

Concatenation, and What One NULL Does to It

|| joins text end to end. Two pipe characters — not a plus, and not the CONCAT function some other databases use.

SELECT name || ' (' || country || ')' AS label
FROM users;                          -- Ada Lovelace (UK)

Every space and bracket is a literal you have to write. SQL adds no spacing of its own, so a label that reads Ada LovelaceUK is a missing ' ' and nothing deeper.

Then the rule that matters. Concatenation obeys module 1-05's NULL arithmetic: anything joined to NULL is NULL — and not "the rest of the string with a gap in it". The entire result disappears.

The name was never missing — it was destroyed on its way through the join. Wrap the nullable column, not the whole expression: COALESCE round the company keeps the label's shape, COALESCE round the result only replaces one blank with another.

Five of the twelve users here have no company. A label built without COALESCE loses five names, and the report shows five empty cells that look like missing people rather than missing companies.

Quick check

An address label is line1 || ', ' || line2 || ', ' || city. Some rows have no line2. What do those rows show?

Integer Division

This is the most costly piece of arithmetic in the language, and it is four characters long.

SELECT 7 / 2;      -- 3
SELECT 21 / 24;    -- 0

An integer divided by an integer gives an integer. The remainder is discarded — not rounded, discarded. No error, no warning, no decimal point.

Now consider what a percentage looks like. It is a count of some things over a count of all the things, and the numerator is always smaller than the denominator. So the naive version is not merely inaccurate:

-- Always zero. Always. For any data.
SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*) AS pct
FROM orders;

21 paid out of 24 orders is 87.5%. Written that way it is 0, and it will be 0 next month and next year too. The saving grace is that a column of zeroes is at least visible; the dangerous variant is one where the numerator sometimes exceeds the denominator, which produces 1s and 2s that look like real data.

The fix is to make one side a real number:

ROUND(100.0 * SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*), 1)
-- 87.5

100.0 rather than 100. One character. Put the multiplication first so the value is already real before the division happens — x / y * 100.0 divides two integers first and then multiplies zero by a hundred.

Zero, always

`paid / total * 100.0`

The division happens first, between two integers. Multiplying the resulting 0 by 100.0 gives 0.0, which looks convincingly like a computed number.

Correct

`100.0 * paid / total`

The first operation is real, so everything after it is. Same operands, same operators, different order.

The conversion rate that was always nought percent

A funnel dashboard showed 0% conversion at every step for two weeks after launch. It was reported as a tracking outage and three people spent a day on the event pipeline. The events were fine; every rate in the query was an integer divided by a larger integer. The fix was a single decimal point, and the reason it took a day is that a plausible wrong number gets debugged in the wrong place.

Quick check

Which of these gives 87.5 for 21 out of 24?

ROUND, CAST, and Doing Them in the Right Order

CAST(value AS REAL) converts explicitly. It is the self-documenting alternative to a stray .0: a reader can see that you knew about integer division rather than guessing that you did.

CAST(x AS INTEGER) goes the other way and truncates towards zero rather than rounding. That catches people out:

CAST(3.9 AS INTEGER)   -- 3
ROUND(3.9)             -- 4.0

Use CAST to change a type and ROUND to change a precision. Reaching for one where you meant the other is an off-by-one that only shows up on some rows.

And the rule that keeps coming back in this part: round last. Rounding is presentation, so it belongs on the value being displayed and nowhere earlier.

Rounded too early

`SUM(ROUND(amount, 0))`

Every row loses up to half a unit before the adding starts. Over a million rows the total is meaningfully wrong, and nothing in the output hints at it.

Rounded last

`ROUND(SUM(amount), 2)`

Add the real values, then present the total to two places. This is also what module 2-03 said about thresholds, for the same reason.

That completes Part 2. You can now turn a table into a number, a number per category, a number per month, and a number that is a percentage of another number — all from one table at a time. Part 3 is about what happens when the answer lives in two.

Quick check

A report totals a million prices and shows the result to the nearest pound. Where should ROUND go?

0 of 9 completed

Loading the tables…

To do

UPPER and LOWER change the case of text; LENGTH returns how many characters it has. None of them change what is stored — they compute a new value for the result set, the way an arithmetic expression did in module 1-04.

Your task: from users, return name, the country in upper case as country, and the length of the name as name_len, ordered by user_id.

query.sql

SELECT
FROM users
ORDER BY user_id;

PostgreSQL

Hint

SELECT name, UPPER(country) AS country, LENGTH(name) AS name_len FROM users ORDER BY user_id; — country is already upper case here, which is fine: the function still runs.

Output

To do

|| joins text end to end. It is two pipe characters, not a plus, and not the CONCAT some other databases use.

SELECT name || ' (' || country || ')' AS label
FROM users;

Literal text goes in single quotes, including the spaces and brackets — SQL will not add spacing for you.

Your task: from products, return one column named label reading like Pro Seat [license] — the name, a space, then the category in square brackets. Ordered by product_id.

query.sql

SELECT
FROM products
ORDER BY product_id;

PostgreSQL

Hint

name || ' [' || category || ']' AS label — the space before the bracket is part of the literal.

Output

To do

Concatenation follows the NULL rule from module 1-05: anything joined to NULL is NULL. Not the rest of the string with a gap in it — the entire result, gone.

Five users have no company. Build a label out of name || ' — ' || company and those five rows come back completely empty, including the name that was perfectly fine.

COALESCE is the fix, exactly as it was for a missing total.

Your task: from users, return user_id and a column label reading Name — Company, using the word Independent in place of a missing company. Ordered by user_id.

query.sql

-- Five of these have no company. None of the twelve should come back blank.
SELECT
FROM users
ORDER BY user_id;

PostgreSQL

Hint

name || ' — ' || COALESCE(company, 'Independent') AS label — wrap the nullable column, not the whole expression.

Output

To do

substr(text, start, length) takes a slice. Positions start at 1, not 0. Leave the length off and it runs to the end.

strpos(text, needle) returns the position of the first occurrence, or 0 if it is not there. Together they cut a string at a character you have to find first.

substr(name, 1, strpos(name, ' ') - 1)   -- everything before the first space

Your task: from users, return name and the first name as first_name — everything before the first space — ordered by user_id.

query.sql

SELECT
FROM users
ORDER BY user_id;

PostgreSQL

Hint

substr(name, 1, strpos(name, ' ') - 1) AS first_name — the minus one is what stops the space itself coming along.

Output

To do

REPLACE(text, find, put) swaps every occurrence. TRIM(text) removes leading and trailing whitespace — the single most common thing wrong with data somebody typed.

Neither changes the stored row. Cleaning on the way out is fine for a report; if the data itself is dirty, fixing it belongs in an UPDATE, which is module 4-04.

Your task: from products, return product_id and the name with the word Seat replaced by Licence as renamed, ordered by product_id.

query.sql

SELECT
FROM products
ORDER BY product_id;

PostgreSQL

Hint

REPLACE(name, 'Seat', 'Licence') AS renamed — rows that do not contain the word come back unchanged, which is what you want.

Output

To do

This is the one to remember. Divide an integer by an integer and Postgres gives you an integer, throwing the remainder away.

SELECT 7 / 2;      -- 3, not 3.5
SELECT 21 / 24;    -- 0

No error, no warning, no decimal point. A percentage written the obvious way — the count of one thing over the count of everything — is always zero, because the numerator is always smaller than the denominator.

The fix is to make one side a real number. Multiplying by 100.0 rather than 100 is the usual trick, because a percentage wants the hundred anyway.

Your task: from orders, return the total number of orders as n, how many are paid as paid, and the paid percentage rounded to one decimal place as pct. One row.

query.sql

-- Written with plain integers, pct comes back as 0. Make one side real.
SELECT COUNT(*) AS n,
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
       -- pct here
FROM orders;

PostgreSQL

Hint

ROUND(100.0 * SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct — the .0 on the 100 is the whole fix.

Output

To do

Postgres has a short cast operator, ::, and it is what you will see in real code: value::numeric, ordered_at::text. The standard CAST(value AS numeric) means the same thing and is more portable; the :: form is more readable once there are two of them in a line.

Casting is how the integer division from the last exercise gets fixed when there is no convenient 100.0 to multiply by. Average quantity per order is SUM(quantity) / COUNT(*) — both integers, so the answer truncates.

Cast to numeric, not to a float. Postgres's two-argument round(value, places) exists only for numeric: round a double precision to two places and you get function round(double precision, integer) does not exist, which is a confusing error for what looks like an arithmetic problem.

Your task: from orders, return the number of orders as n, the total quantity as units, and the average units per order as avg_units — rounded to two decimal places. One row.

query.sql

-- Written with plain integers, avg_units truncates to 1. It is really 1.67.
SELECT COUNT(*)      AS n,
       SUM(quantity) AS units,
       -- avg_units here
FROM orders;

PostgreSQL

Hint

ROUND(SUM(quantity)::numeric / COUNT(*), 2) AS avg_units — cast the numerator to numeric and the division, and the rounding, both behave.

Output

To do

ROUND(x, n) rounds to n decimal places. ABS(x) drops the sign. Both are for presentation, and both are safest applied last — module 2-03 showed what rounding before a comparison does to a threshold.

Your task: from products, return name, price, and the price rounded to the nearest whole number as rounded, most expensive first.

query.sql

SELECT
FROM products
ORDER BY price DESC;

PostgreSQL

Hint

ROUND(price, 0) AS rounded — a second argument of 0 means whole numbers.

Output

To do

Everything in this module composes with everything in the last four. A function can produce the grouping key, sit inside an aggregate, or shape the label — and a percentage inside a grouped query needs the same 100.0 as a percentage over the whole table.

Your task: from orders, return each status, the count as n, and each status's share of all orders as pct — rounded to one decimal place — largest share first.

The denominator is the total number of orders in the table, which is 24. Write it as 24.0 rather than 24.

query.sql

SELECT
FROM orders
GROUP BY status
ORDER BY

PostgreSQL

Hint

ROUND(100.0 * COUNT(*) / 24.0, 1) AS pct — then ORDER BY n DESC.

Output

The clean product list

To do

The website team wants the catalogue in a form they can drop straight onto a page: one readable label per product, the price to two decimal places, and each product's share of the total catalogue value.

Your task: from products, return exactly these three columns in this order:

label — the name, a space, then the category in brackets, in the shape Pro Seat (license)
price — the price rounded to two decimal places
share — the price as a percentage of the total price of all eight products, rounded to one decimal place

Most expensive first. The total of all eight prices is 472; write the denominator as 472.0 so the division stays real, and check that your shares add up to roughly 100 before you call it done.

query.sql

-- Readable label, price to 2dp, share of catalogue value to 1dp.
SELECT
FROM products
ORDER BY

PostgreSQL

Hint

name || ' (' || category || ')' AS label, then ROUND(price, 2) AS price, then ROUND(100.0 * price / 472.0, 1) AS share, then ORDER BY price DESC.

Output

Sign in to open this module

Text & Number Functions

Cutting, Measuring and Cleaning

UPPER / LOWER

LENGTH(text)

substr(text, start, len)

strpos(text, needle)

TRIM / REPLACE

Clean on the way out, or clean the data?

Quick check

Concatenation, and What One NULL Does to It

Quick check

Integer Division

paid / total * 100.0

100.0 * paid / total

The conversion rate that was always nought percent

Quick check

ROUND, CAST, and Doing Them in the Right Order

SUM(ROUND(amount, 0))

ROUND(SUM(amount), 2)

Quick check

Change the case, measure the length

Glue values together

One NULL empties the whole string

Cut a piece out

Clean it up

The division that returns zero

Say what type you want

Rounding, and the sign

Functions inside a grouped query

The clean product list

Notification

`UPPER` / `LOWER`

`LENGTH(text)`

`substr(text, start, len)`

`strpos(text, needle)`

`TRIM` / `REPLACE`

`paid / total * 100.0`

`100.0 * paid / total`

`SUM(ROUND(amount, 0))`

`ROUND(SUM(amount), 2)`