Functions: Text, Numbers, Dates & CASE
Clean and reshape values inside a query: text functions (UPPER, LENGTH, SUBSTR, TRIM, REPLACE, joining text), rounding, dates (extracting the month, adding days, the gap between dates), CASE for if/else logic, COALESCE for missing values, and CAST.
Functions change values row by row
A function takes values and returns a new one, for every row: UPPER(name) gives each name in capitals. Functions can be used in SELECT, WHERE and ORDER BY, and nested inside each other. (Functions that summarise many rows into one, like SUM and COUNT, are in the next lesson.)
Text functions
| Function | Example | Result |
|---|---|---|
UPPER, LOWER | UPPER('Sita') | SITA |
LENGTH | LENGTH('Brisbane') | 8 |
SUBSTR(text, start, length) | SUBSTR('2025-06-14', 1, 7) | 2025-06 (positions start at 1) |
TRIM | TRIM(' hi ') | hi |
REPLACE | REPLACE('a-b', '-', '/') | a/b |
|| (join) | name || ' (' || country || ')' | Mia Chen (Australia) |
INSTR(text, find) | INSTR('Mia Chen', ' ') | 4 (position, 0 if missing) |
Joining text: SQLite and PostgreSQL use ||; MySQL and SQL Server use CONCAT(a, b) (PostgreSQL has CONCAT too). PostgreSQL's SUBSTRING and POSITION do the jobs of SUBSTR and INSTR.
Numbers
ROUND(x, 2) rounds to 2 decimals, ABS(x) removes the sign, and % gives the remainder. Remember that whole-number division drops the decimals: ROUND(SUM(x) * 1.0 / COUNT(*), 2) is the safe way to average by hand. CAST(x AS REAL) (or CAST(x AS INTEGER)) converts between types.
Dates
SQLite stores dates as text in the ISO format '2025-06-14', which sorts and compares correctly. Its date functions:
| Need | SQLite | PostgreSQL |
|---|---|---|
| today | date('now') | CURRENT_DATE |
| year, month | strftime('%Y', d), strftime('%m', d) | EXTRACT(YEAR FROM d) |
| "2025-06" (year-month) | strftime('%Y-%m', d) | to_char(d, 'YYYY-MM') |
| add 30 days | date(d, '+30 days') | d + INTERVAL '30 days' |
| days between | julianday(b) - julianday(a) | b - a |
| day of week (0 = Sunday) | strftime('%w', d) | EXTRACT(DOW FROM d) |
Grouping by strftime('%Y-%m', order_date) is how you get monthly totals, which you will do in the next lesson.
CASE: if/else inside a query
CASE picks a value with conditions, checked from the top; the first true one wins. Use it to make categories, labels and flags:
Without ELSE, rows that match nothing get NULL. A neat trick: SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) counts the cancelled orders.
COALESCE and NULLIF
COALESCE(a, b, …) returns the first value that is not NULL: COALESCE(city, 'unknown') fills in missing cities. NULLIF(a, b) returns NULL when a equals b, which avoids dividing by zero: total / NULLIF(count, 0).
Try it: functions
Run each preset, then combine functions: the length of each product name in capitals, or the orders placed on a weekend.
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Show each customer's name and a column first_name with just the part before the first space.
Show each product's name and a column band: 'premium' if the price is 300 or more, 'mid' if 50 or more, otherwise 'budget'.
Show order_id and a column month in the form 2025-06 for every order placed in June 2025.
Show each customer's name and a column place: their city, or the text unknown if the city is NULL.
Summary
Key words
- function
- takes values and returns a new value for each row
- ||
- joins text in SQLite and PostgreSQL
- SUBSTR
- a piece of text; positions start at 1
- strftime
- SQLite's date formatting and extracting function
- CASE
- if/else logic that returns a value
- COALESCE
- the first value that is not NULL
- CAST
- converts a value to another type
Common mistakes
- Counting text positions from 0: SQL counts from 1.
- Using
+to join text: in SQLite'a' + 'b'is 0. Use||. - Dates stored as "14/06/2025": they do not sort. Store and compare dates as YYYY-MM-DD.
- Forgetting END at the end of CASE.
- Expecting a function from one dialect to exist in another (for example strftime in PostgreSQL).
Quiz
1. What is SUBSTR('Brisbane', 1, 3)?
2. How do you join first and last name with a space in SQLite?
3. Which gives the year-month of a date in SQLite?
4. What does COALESCE(NULL, NULL, 'x') return?
5. A CASE with no ELSE, where no WHEN matches, returns…