Expressions and functions

Why it matters

Real data is rarely in the exact shape you need. Emails arrive with stray capitals and spaces, names are split across two columns, prices need tax added, and dates need to become months or years for a report. SQL lets you compute new columns on the fly with expressions and functions, without changing the stored data.

Expressions: calculated columns

Anywhere you can name a column in SELECT, you can write an expressionA calculation written in place of a column, such as price * quantity, whose result becomes a new output column. instead. Give it an alias so the result column has a useful name.

SELECT name,
       price,
       quantity,
       price * quantity AS line_total
FROM order_items;

Arithmetic uses +, -, *, / and % (remainder). One trap: in SQLite, PostgreSQL and SQL Server, dividing two integers gives an integer. 7 / 2 is 3, not 3.5. Make one side a decimal to get a decimal result: 7 / 2.0 or CAST(7 AS REAL) / 2 gives 3.5.

Math functions

• ROUND(x, n) rounds to n decimal places: ROUND(19.987, 2) is 19.99.
• ABS(x) gives the absolute value.
• MIN(a, b) and MAX(a, b) with two or more arguments return the smaller or larger value in SQLite (other engines call these LEAST and GREATEST). With one argument they are aggregates, covered in the next module.

SELECT name, ROUND(price * 1.08, 2) AS price_with_tax
FROM products;

String functions

Text functions are where engines differ most, so here are the SQLite forms with notes on others:

Functions nest, and they run from the inside out. To clean an email address, trimA string function that removes spaces from both ends of a text value. it first and then lower-case it:

SELECT LOWER(TRIM(email)) AS email_clean
FROM customers;

' Ada.Lovelace@Example.COM ' becomes 'ada.lovelace@example.com'.

To get the domain part of an email, find the @ and take everything after it:

SELECT email,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers;

Leaving out the length argument of SUBSTR takes the rest of the string.

Dates in SQLite

SQLite has no separate date type. Dates are usually stored as text in ISO format, 'YYYY-MM-DD' (for example '2024-03-15'), which sorts correctly as text. Functions work on that format:

• date('2024-03-15', '+30 days') gives '2024-04-14'.
• strftime('%Y', signup_date) gives the year as text, '2024'; '%m' gives the month, '%Y-%m' gives '2024-03'.
• julianday(b) - julianday(a) gives the number of days between two dates.

SELECT id,
       strftime('%Y-%m', order_date) AS order_month,
       julianday(shipped_date) - julianday(order_date) AS days_to_ship
FROM orders;

Other engines have real DATE types and different functions (EXTRACT(YEAR FROM d) in PostgreSQL, YEAR(d) in MySQL and SQL Server, DATEDIFF for differences). The idea is the same; check your engine's documentation for names. Always store dates in ISO format: '03/15/2024' sorts wrongly and is ambiguous across countries.

Converting types with CAST

CAST(value AS type) converts between types. strftime('%Y', d) returns text; CAST(strftime('%Y', d) AS INTEGER) returns a number you can do arithmetic with.

Worked example

A marketing team wants a contact list with full names, clean emails and the year each customer joined:

SELECT id,
       first_name || ' ' || last_name AS full_name,
       LOWER(TRIM(email))             AS email_clean,
       CAST(strftime('%Y', signup_date) AS INTEGER) AS signup_year
FROM customers;

The stored data does not change. You are only shaping the output. Fixing the stored data uses UPDATE, covered in module 4.

Common mistakes

• Integer divisionDividing two whole numbers in SQLite, PostgreSQL or SQL Server drops the remainder, so 7 / 2 gives 3 rather than 3.5.. 5 / 2 is 2 in SQLite. Use 5 / 2.0.
• Using + to join strings. In SQLite 'a' + 'b' tries arithmetic and returns 0. Use ||.
• Forgetting aliases. An unnamed expression gets a column name like LOWER(TRIM(email)), which is hard to use later.
• Non-ISO dates. Date functions expect YYYY-MM-DD; other formats return NULL.
• Expecting functions to be portable. Core arithmetic is; string and date function names vary by engine.

Quick recap

• Expressions compute new columns; name them with AS.
• Watch integer division; use a decimal to get decimals.
• ||, UPPER, LOWER, TRIM, LENGTH, SUBSTR, REPLACE and INSTR reshape text.
• SQLite dates are ISO text; date, strftime and julianday work on them.
• CAST converts types.

Try it

Open the Do tab and build a clean contact list from a messy customer table.