Scalar and aggregate functions — Sets and functions

Scalar functions

A scalar function transforms one value per row and returns another value.

Text strings

Function	What it does
`UPPER(s)` / `LOWER(s)`	converts to uppercase / lowercase
`LENGTH(s)`	number of characters
`SUBSTR(s, start, n)`	substring (the index starts at 1)
`TRIM(s)`	removes leading and trailing spaces
`a

SELECT UPPER(name) || ' (' || category || ')' AS label
FROM products;

Numbers

ROUND(x, n) rounds x to n decimals (ROUND(149.99, 1) → 150.0).
ABS(x) returns the absolute value.

SELECT name, ROUND(price, 0) AS rounded_price FROM products;

GROUP_CONCAT: aggregating text

GROUP_CONCAT is an aggregate function (like COUNT or SUM): it combines the values of a group into a single string, separated by commas (or by the separator you give as the second argument). It is used with GROUP BY:

SELECT category, GROUP_CONCAT(name) AS products
FROM products
GROUP BY category
ORDER BY category;

Date functions (mention)

SQLite has no dedicated "date" type: it stores dates as text ('2026-06-22') or numbers, and manipulates them with functions:

date('now') returns the current date.
strftime('%Y', date) extracts parts of a date with a format (here, the year).

SELECT strftime('%Y-%m', date('now')) AS year_month;

Examples

A readable label per product

SELECT UPPER(name) || ' - ' || category AS label
FROM products
ORDER BY id;