SQL & Databases
Topic 5 · Querying data

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.

Show:
In this topic
Beginner

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.)

Beginner

Text functions

FunctionExampleResult
UPPER, LOWERUPPER('Sita')SITA
LENGTHLENGTH('Brisbane')8
SUBSTR(text, start, length)SUBSTR('2025-06-14', 1, 7)2025-06 (positions start at 1)
TRIMTRIM(' hi ')hi
REPLACEREPLACE('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.

Beginner

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.

Beginner

Dates

SQLite stores dates as text in the ISO format '2025-06-14', which sorts and compares correctly. Its date functions:

NeedSQLitePostgreSQL
todaydate('now')CURRENT_DATE
year, monthstrftime('%Y', d), strftime('%m', d)EXTRACT(YEAR FROM d)
"2025-06" (year-month)strftime('%Y-%m', d)to_char(d, 'YYYY-MM')
add 30 daysdate(d, '+30 days')d + INTERVAL '30 days'
days betweenjulianday(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.

Beginner

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:

SELECT name, price, CASE WHEN price >= 300 THEN 'premium' WHEN price >= 50 THEN 'mid-range' ELSE 'budget' END AS price_band FROM products;

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.

Beginner

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

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.

Beginner

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)

Exercise 1. First names

Show each customer's name and a column first_name with just the part before the first space.

Exercise 2. Price bands

Show each product's name and a column band: 'premium' if the price is 300 or more, 'mid' if 50 or more, otherwise 'budget'.

Exercise 3. Orders per month

Show order_id and a column month in the form 2025-06 for every order placed in June 2025.

Exercise 4. Fill in missing cities

Show each customer's name and a column place: their city, or the text unknown if the city is NULL.

Beginner

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).
Check yourself

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…

Ready