Built-in functions for text, numbers and dates

Functions such as UPPER, LENGTH, SUBSTR, ROUND and the date functions transform a value inside a query — one value in, one value out, for each row.

Why it matters

Data rarely arrives in the shape you need: names in mixed case, prices that need rounding, dates you want as "2024-03" or as "how many days ago". Functions do these small transformations inside the query.

The idea

A function takes values in brackets and returns one value.
Text: UPPER and LOWER change case, LENGTH counts characters, SUBSTR(text, start, length) cuts out a piece (counting from 1), TRIM removes spaces at the ends, and REPLACE swaps text.
Numbers: ROUND(x, 2) rounds to 2 decimal places, ABS removes the minus sign.
Dates are where databases differ most: SQLite uses strftime and julianday; PostgreSQL uses DATE_TRUNC, EXTRACT and date subtraction.

These run once per row — unlike aggregate functions, which turn many rows into one (Unit 2).

Kitchen tools. A peeler (TRIM), a slicer (SUBSTR), a measuring spoon (ROUND). Each one takes one ingredient and gives back a changed version of it.

Kitchen tools are the same in every kitchen. Date functions are not the same in every database — so in an interview, say which database you are assuming, and the interviewer will usually accept any reasonable syntax.

Syntax

UPPER(text)   LENGTH(text)   SUBSTR(text, start, length)
ROUND(number, places)
strftime('%Y-%m', date)   -- SQLite: the year and month

How it works

  1. UPPER('Dara') is 'DARA'; LENGTH('Dara') is 4; SUBSTR('Dara', 1, 3) is 'Dar' (start at character 1, take 3).
  2. salary / 12.0 gives a long decimal; ROUND(…, 2) keeps 2 decimal places.
  3. strftime('%Y-%m', '2024-01-21') is '2024-01': the year and month, ready to group by.
  4. julianday turns a date into a day number, so subtracting two of them gives the days between.
  5. Functions can be nested: UPPER(SUBSTR(name, 1, 1)) is the first letter in capitals.

Key points

Common mistakes

Not true: Rounding each value first and then adding them gives the same total as adding first.
In fact: Every rounding adds a small error, and the errors add up. Add the exact values first, and round once at the end.

In the interview

Scalar functions transform one value per row. There are string functions like UPPER, SUBSTRING and TRIM, numeric ones like ROUND, and date functions for truncating to a month or finding the days between two dates. Date syntax is the least portable part of SQL, so in an interview I name the dialect I am assuming: usually PostgreSQL, with DATE_TRUNC and date subtraction. I also avoid wrapping indexed columns in functions inside WHERE, because that blocks the index.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.