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 monthHow it works
UPPER('Dara')is 'DARA';LENGTH('Dara')is 4;SUBSTR('Dara', 1, 3)is 'Dar' (start at character 1, take 3).- salary / 12.0 gives a long decimal;
ROUND(…, 2)keeps 2 decimal places. strftime('%Y-%m', '2024-01-21')is '2024-01': the year and month, ready to group by.- julianday turns a date into a day number, so subtracting two of them gives the days between.
- Functions can be nested:
UPPER(SUBSTR(name, 1, 1))is the first letter in capitals.
Key points
- A row function runs once per row and returns one value; an aggregate function squashes many rows into one.
- Text positions start at 1, not 0:
SUBSTR('Dara', 1, 3)is 'Dar'. ROUND(x, 2)for display; divide by a decimal (12.0) to avoid integer division first.- Date functions differ the most between databases. Know your target database's month-truncation and date-difference functions.
- Wrapping a column in a function inside WHERE (
WHERE UPPER(name) = …) stops a normal index on that column being used.
Common mistakes
- Counting text positions from 0, as in Python.
- Rounding before summing, which adds up rounding errors. Sum first, round at the end.
- Comparing dates stored as '06/01/2024' text — they compare as text, not as dates.
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.