Функции для строк, дат и чисел

Данные редко хранятся именно в том виде, который нам нужен. Выделить домен из e-mail, сделать короткое имя вроде «A. Məmmədova», вычислить крайний срок оплаты, добавить к цене НДС и округлить — всё это можно сделать в коде программы, но часто проще и быстрее прямо в запросе. В этом уроке мы разберём три группы функций. Внимание: функции — самая «диалектная» часть SQL, поэтому для каждой мы приводим аналог в MySQL и PostgreSQL.

Строковые функции

• LENGTH(s) — число символов; UPPER(s), LOWER(s) — заглавные и строчные буквы.
• SUBSTR(s, начало, длина) — часть текста; позиции считаются с 1, без длины берётся до конца.
• INSTR(s, подстрока) — первая позиция подстроки (0, если её нет); REPLACE(s, старое, новое) — замена; TRIM(s) — убирает пробелы по краям.

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

Функции для дат

В SQLite нет отдельного типа для дат: даты хранятся как текст в формате YYYY-MM-DD. strftime(формат, дата) извлекает части даты (%Y — год, %m — месяц, %w — день недели, 0 = воскресенье), а 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

Чтобы посчитать дни между двумя датами, превращаем их в числа с помощью julianday — число дней от фиксированной начальной точки — и вычитаем. Ниже мы находим, сколько дней прошло у каждого покупателя от регистрации до первого заказа.

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

Математические функции и округление

Деление двух целых чисел в SQLite, PostgreSQL и SQL Server снова даёт целое: 7 / 2 = 3, а % даёт остаток. Это полезно в задачах вроде упаковки: 500 тетрадей в коробках по 24 — это 20 полных коробок и 20 тетрадей в остатке. Добавляя к цене НДС 18%, округляем результат до гяпиков с помощью 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

Математические функции вроде SQRT, POWER, FLOOR, CEIL и LN в PostgreSQL и MySQL есть всегда. В SQLite их добавили в версии 3.35, но они доступны только в сборках со специальным параметром, поэтому переносимым запросам для SQLite надёжнее обходиться ROUND, ABS, CAST и %.

«Словарь» между диалектами

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