Built-in functions for text, numbers and dates

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  A["one value per row<br/>Dara · 52000 · 2024-01-21"] --> F["a function<br/>UPPER · ROUND · strftime"] --> B["one new value per row<br/>DARA · 4333.33 · 2024-01"]

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 (Phase 2).

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT name, UPPER(name) AS shout, LENGTH(name) AS letters, SUBSTR(name, 1, 3) AS first_three
FROM employees
WHERE dept_id = 2
ORDER BY name;

SELECT name, salary, ROUND(salary / 12.0, 2) AS monthly
FROM employees
WHERE dept_id = 3 OR dept_id IS NULL
ORDER BY name;

SELECT order_id, order_date,
       strftime('%Y-%m', order_date) AS month,
       julianday('2024-03-31') - julianday(order_date) AS days_before_end_of_march
FROM orders
WHERE customer_id = 2
ORDER BY order_id;

Must-know 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.

In other databases

• Month of a date: PostgreSQL DATE_TRUNC('month', d) or TO_CHAR(d, 'YYYY-MM'); MySQL DATE_FORMAT(d, '%Y-%m'); SQL Server FORMAT(d, 'yyyy-MM'); SQLite strftime('%Y-%m', d).
• Days between: PostgreSQL d2 - d1 (dates); MySQL DATEDIFF(d2, d1); SQL Server DATEDIFF(day, d1, d2); SQLite julianday(d2) - julianday(d1).
• Year of a date: EXTRACT(YEAR FROM d) in PostgreSQL and MySQL; YEAR(d) in MySQL and SQL Server; strftime('%Y', d) in SQLite.
• Text length is LENGTH in most databases but LEN in SQL Server.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  SQLite docs — Date And Time Functions

  official documentation, free

• Read

  PostgreSQL docs — Date/Time Functions and Operators

  official documentation, free

• Practice

  SQLZoo — SELECT from WORLD: string functions

  free exercises

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the difference between a row function like UPPER and an aggregate like SUM.

   Show answer

   A row function such as UPPER works on one value from one row and gives one value back. 8 employees in, 8 rows out, each with its own upper-case name.

   An aggregate such as SUM reads a whole column across many rows and gives back one value for the group. 8 salaries in, one total out. The rows collapse.

   That is why you can write SELECT name, UPPER(name), but not SELECT name, SUM(salary) without GROUP BY: one name per row can't sit next to one total for all rows.

2. Writefrom a blank page, then run it

   Write a query that shows each customer's name and the part of their email before the @. (Hint: INSTR(email, '@') gives the position of the @.)

   Show answer

   INSTR(email, '@') finds where the @ is. Take the characters from position 1 up to one before it. For priya@mail.com the @ is at position 6, so take 5 characters.

   sqlCopy

   SELECT name, email, SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
   FROM customers
   ORDER BY customer_id;

   Try it

   6 rows

   name	email	username
   Priya	priya@mail.com	priya
   Tom	tom@mail.com	tom
   Lena	lena@mail.com	lena
   Omar	omar@mail.com	omar
   Zoe	zoe@mail.com	zoe
   Tom	tom@mail.com	tom

3. Predictsay it before you run it

   What does SELECT SUBSTR('Headphones', 5, 5), LENGTH(TRIM(' Pen ')), ROUND(2.675, 1); return?

   Show answer

   'phone', 3 and 2.7.

   • SUBSTR('Headphones', 5, 5): start at character 5 (counting from 1: H-e-a-d-p, so it starts at the p) and take 5 characters: "phone".
   • TRIM removes the spaces at both ends, leaving "Pen", which has 3 letters.
   • ROUND(2.675, 1) keeps one decimal place: 2.7.

   sqlCopy

   SELECT SUBSTR('Headphones', 5, 5), LENGTH(TRIM('  Pen  ')), ROUND(2.675, 1);

   Try it

   1 row

   SUBSTR('Headphones', 5, 5)	LENGTH(TRIM(' Pen '))	ROUND(2.675, 1)
   phone	3	2.7

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   How many days passed between each employee's hire_date and '2024-01-01'? What happens if one of the dates is NULL?

   Show answer

   Subtract the julianday numbers: each date becomes a count of days, so the difference is days between. Asha had been there 1,767 days by 1 January 2024.

   If either date is NULL, julianday gives NULL, and the subtraction gives NULL for that row. There is no error, just a blank. Decide what you want: drop those rows with WHERE hire_date IS NOT NULL, or show a default with COALESCE. (In SQLite, SELECT julianday('2024-01-01') - julianday(NULL); returns NULL.)

   sqlCopy

   SELECT name, hire_date,
          julianday('2024-01-01') - julianday(hire_date) AS days_to_2024
   FROM employees
   ORDER BY emp_id;

   Try it

   8 rows

   name	hire_date	days_to_2024
   Asha	2019-03-01	1767
   Ben	2020-06-15	1295
   Chen	2021-01-10	1086
   Dara	2020-02-20	1411
   Eli	2022-07-01	549
   Fatima	2021-09-12	841
   Gus	2023-04-03	273
   Hana	2022-11-30	397

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

Scalar functions transform one value per row: 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 — and I avoid wrapping indexed columns in functions inside WHERE, because that blocks the index.

Follow-up questions

Tick each check when you can do it without notes