String, date and math functions

Data is rarely stored in exactly the form we need. Extracting the domain from an email, making a short name like “A. Mammadova”, calculating a payment deadline, adding VAT to a price and rounding it — all of this can be done in application code, but it is often simpler and faster to do it right in the query. In this lesson we cover three groups of functions. Be careful: functions are the part of SQL that differs most between dialects, so for each one we also give the MySQL and PostgreSQL equivalent.

String functions

• LENGTH(s) — the number of characters; UPPER(s), LOWER(s) — upper and lower case.
• SUBSTR(s, start, length) — part of the text; positions start at 1, and without a length it goes to the end.
• INSTR(s, sub) — the first position of a substring (0 if absent); REPLACE(s, old, new) — replacement; TRIM(s) — removes spaces at both ends.

SELECT UPPER(last_name) AS upper_name,
       LENGTH(last_name) AS len,
       SUBSTR(first_name, 1, 1) || '. ' || last_name AS short_name,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM students
WHERE id IN (1, 3, 4)
ORDER BY id;

upper_name | len | short_name | domain
MəMMəDOVA | 9 | A. Məmmədova | example.com
HüSEYNOVA | 9 | L. Hüseynova | example.com
QULIYEV | 7 | E. Quliyev | NULL

Date functions

SQLite has no separate date type: dates are stored as text in the YYYY-MM-DD format. strftime(format, date) extracts parts of a date (%Y — year, %m — month, %w — day of the week, 0 = Sunday), and date(date, modifier...) shifts a date: '+30 days', '-1 month', 'start of month'.

SELECT id, order_date,
       strftime('%Y', order_date) AS year,
       strftime('%m', order_date) AS month,
       strftime('%w', order_date) AS weekday,
       date(order_date, '+30 days') AS pay_until
FROM orders
WHERE id <= 4
ORDER BY id;

id | order_date | year | month | weekday | pay_until
1 | 2025-01-15 | 2025 | 01 | 3 | 2025-02-14
2 | 2025-01-15 | 2025 | 01 | 3 | 2025-02-14
3 | 2025-02-02 | 2025 | 02 | 0 | 2025-03-04
4 | 2025-02-10 | 2025 | 02 | 1 | 2025-03-12

To count the days between two dates, we turn them into numbers with julianday — the number of days since a fixed starting point — and subtract. Below we find how many days each customer waited between signing up and their first order.

SELECT c.name, c.joined_on,
       MIN(o.order_date) AS first_order,
       CAST(julianday(MIN(o.order_date)) - julianday(c.joined_on) AS INTEGER)
         AS days_to_first_order
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.joined_on
ORDER BY days_to_first_order, c.name;

name | joined_on | first_order | days_to_first_order
Zəhra Hüseynli | 2025-02-14 | 2025-04-09 | 54
Mehmet Kaya | 2025-04-01 | 2025-06-12 | 72
John Carter | 2024-08-30 | 2025-03-18 | 200
Olga Ivanova | 2024-06-18 | 2025-03-05 | 260
Emre Yılmaz | 2024-05-05 | 2025-02-10 | 281
Lalə Əhmədova | 2024-03-22 | 2025-02-02 | 317
Anar Mustafayev | 2024-01-10 | 2025-01-15 | 371

Math functions and rounding

Dividing two integers gives an integer again in SQLite, PostgreSQL and SQL Server: 7 / 2 = 3, and % gives the remainder. That is useful for problems like packing: 500 notebooks in boxes of 24 make 20 full boxes with 20 notebooks left over. When we add 18% VAT to a price, we round the result to hundredths of a manat with ROUND(x, 2).

SELECT name, stock,
       stock / 24 AS full_boxes,
       stock % 24 AS left_over,
       ROUND(price * 1.18, 2) AS price_with_vat
FROM products
WHERE category IN ('Stationery', 'Accessories')
ORDER BY id;

name | stock | full_boxes | left_over | price_with_vat
Notebook | 500 | 20 | 20 | 3.78
Pen set | 300 | 12 | 12 | 9.32
Backpack | 60 | 2 | 12 | 64.9
Water bottle | 120 | 5 | 0 | 14.16

SELECT 7 / 2               AS int_div,
       7 / 2.0             AS real_div,
       ROUND(2.5)          AS half_up,
       ROUND(-2.5)         AS half_down,
       CAST(-12.9 AS INTEGER) AS truncated;

int_div | real_div | half_up | half_down | truncated
3 | 3.5 | 3 | -3 | -12

Math functions such as SQRT, POWER, FLOOR, CEIL and LN are always available in PostgreSQL and MySQL. They were added to SQLite in version 3.35, but only in builds compiled with a special option, so portable queries for SQLite are safer if they stick to ROUND, ABS, CAST and %.

A “dictionary” between dialects

SELECT email, first_name
FROM students
WHERE email IS NOT NULL
ORDER BY email;

login | first_name
aysel | Aysel
fidan | Fidan
gunay | Günay
kamran | Kamran
leyla | Leyla
murad | Murad
nigar | Nigar
orxan | Orxan
rashad | Rəşad
sabina | Səbinə

SELECT SUBSTR(order_date, 1, 7) AS month,
       COUNT(*) AS orders
       -- add month_end
FROM orders
GROUP BY month
ORDER BY month;

month | orders | month_end
2025-01 | 2 | 2025-01-31
2025-02 | 2 | 2025-02-28
2025-03 | 2 | 2025-03-31
2025-04 | 2 | 2025-04-30
2025-05 | 1 | 2025-05-31
2025-06 | 2 | 2025-06-30
2025-07 | 1 | 2025-07-31