Строки в SQL: склейка, поиск и приведение к одному виду

← назад к разделу

Данные приходят к нам от людей, а люди пишут по-разному. Один покупатель ввёл фамилию капсом, другой оставил лишний пробел в конце, третий ищет наушники, набрав «наушник». В базе при этом всё строго: 'Иванов' и 'ИВАНОВ' две разные строки, и обычное сравнение их не сведёт.

Вторая половина работы со строками не поиск, а сборка: показать имя и фамилию одной строкой, собрать состав заказа в одну ячейку отчёта, вытащить номер из составного идентификатора. Всё это делается в самом запросе, и делается десятком функций, которых хватает почти на все задачи.

Склейка строк и пустота от NULL

Две строки соединяет оператор ||:

живой пример

SELECT first_name || ' ' || last_name AS full_name FROM customer;

Работает, пока в одной из колонок не окажется NULL. Тогда NULL становится и результат всей склейки: пустота заражает выражение целиком, и вместо строки в отчёте появляется пустая ячейка.

В customer заполнены все колонки, так что посмотрим на платежи: причина возврата refund_reason есть ровно у одного платежа из шестнадцати.

живой пример

SELECT id || ' — ' || refund_reason AS note FROM payments;

Пятнадцать строк выйдут пустыми, и это не «номер и прочерк»: исчезает и сам номер платежа, хотя он-то на месте.

Отсюда два рабочих приёма. Первый: закрыть дырку заранее через COALESCE: id || ' — ' || COALESCE(refund_reason, 'без возврата'). Второй, чаще удобнее, функция concat_ws: она принимает разделитель первым аргументом, пропускает NULL и не ставит лишних пробелов:

живой пример

SELECT concat_ws(' · ', id, status, refund_reason) AS note
FROM payments;

Число в склейку PostgreSQL пропустит и сам: 'Заказ ' || id || ' на сумму ' || total_amount вернёт «Заказ ord-01 на сумму 4990.00». Выручает соседний текст, от него база и отталкивается; склеить два числа между собой (1 || 2) она откажется. Поэтому приведение пишут явно, чтобы не проверять каждый раз, хватило ли текста по соседству:

живой пример

SELECT 'Заказ ' || id || ' на сумму ' || total_amount::text || ' ₽' AS line
FROM orders;

Для денег в отчёте обычно ещё округляют: round(total_amount, 2)::text. Если нужен строгий формат с разделителями разрядов, это to_char(total_amount, '999 999.99'). Только шаблон здесь не подсказка, а жёсткая рамка: число, которое в неё не влезло, функция молча заменяет решётками: to_char(1234567.891, '999 999.99') вернёт ### ###.##. Берите шаблон с запасом, иначе однажды решётки приедут прямо в отчёт.

Один вид для текста от людей

'Смирнова' и 'СМИРНОВА' для базы разные, и WHERE last_name = 'Смирнова' вторую не найдёт. Приводить к одному виду надо обе стороны сравнения, а не одну: если опустить только колонку, а искомое слово оставить капсом, они всё равно не совпадут. Отсюда привычная пара lower(trim(колонка)) = lower(trim(искомое)): lower убирает разницу в регистре, trim лишние пробелы по краям, которые человек не видит, а база считает частью строки.

живой пример

SELECT id, initcap(lower(last_name)) AS last_name
FROM customer
WHERE lower(trim(last_name)) = lower('СМИРНОВА');

В выводе initcap делает первую букву каждого слова прописной, и порядок важен: сначала lower приводит всё к строчным, потом initcap, иначе «СМИРНОВА» так и останется капсом. Для пробелов с одной стороны есть ltrim и rtrim, для прописных upper.

Есть колонки, где ничего приводить не нужно. Тип citext сравнивает без учёта регистра сам, и customer.email в песочнице объявлен именно так: WHERE email = 'ANNA@EXAMPLE.COM' найдёт anna@example.com без lower. Так делают для почты и логинов, где регистр по определению не значим, и lower вокруг такой колонки не вредит, но и не нужен. Второй особый тип, char(n), наоборот, добавляет проблем: он добивает строку пробелами до полной длины, и 'ок' в char(10) хранится как 'ок '. Поэтому в новых таблицах используют text или varchar; подробности в статье Строковые типы в PostgreSQL.

Поиск по части строки: LIKE и ILIKE

LIKE сравнивает с шаблоном, в котором % означает «любое количество любых символов», а _ ровно один символ:

-- начинается на «Науш»
WHERE title LIKE 'Науш%'
-- содержит «наушник» в любом месте
WHERE title LIKE '%наушник%'
-- заканчивается на «Pro»
WHERE title LIKE '%Pro'

LIKE чувствителен к регистру, ILIKE нет (буква I от «insensitive»). Для поиска по витрине почти всегда нужен второй: покупатель не обязан угадывать, с какой буквы записано название.

живой пример

SELECT id, title, price FROM products WHERE title ILIKE '%наушник%';

Когда искать нужно сам знак процента или подчёркивание, например скидку «50%» в описании, шаблон перестаёт работать: % в нём означает «что угодно». Знак экранируют обратной косой чертой, WHERE description LIKE '%50\%%', а если косая черта сама встречается в данных, объявляют другой символ: LIKE '%50#%%' ESCAPE '#'.

Шаблон с известным началом база проверяет по обычному индексу, как поиск по алфавитному указателю. Шаблон, начинающийся с %, по индексу проверить нельзя: база не знает, с чего начинается строка, и читает все строки таблицы. На каталоге в сотню товаров это незаметно, на миллионе секунды. Когда поиск по подстроке нужен по-настоящему, ставят триграммный индекс из расширения pg_trgm или переходят на полнотекстовый поиск: первый разобран в статье про типы индексов, второй в статье про полнотекстовый поиск.

Замена и чистка: replace и regexp_replace

Телефон, который человек ввёл как +7 (999) 123-45-67, надо сохранить как +79991234567, иначе один и тот же номер окажется в базе в пяти написаниях. Простую замену делает replace(строка, что, на_что): replace(id, '-', '/') превратит ord-2026-0042 в ord/2026/0042. Когда убрать нужно не один знак, а целый класс, берут regexp_replace с регулярным выражением:

живой пример

SELECT regexp_replace('+7 (999) 123-45-67', '[^0-9+]', '', 'g') AS phone;

Выражение [^0-9+] значит «любой символ, кроме цифр и плюса», а флаг 'g' заменяет все вхождения, без него только первое. Ту же функцию берут, чтобы схлопнуть двойные пробелы в названии, regexp_replace(title, '\s+', ' ', 'g'), и чтобы убрать всё лишнее перед сравнением. Правило то же, что и с регистром: чистить надо при записи, а не в каждом запросе, иначе индекс по колонке не поможет.

Разобрать строку на части

Идентификаторы часто составные: ord-2026-0042 это префикс, год, номер. Их разбирают по разделителю функцией split_part(строка, разделитель, номер куска):

живой пример

SELECT id,
       split_part(id, '-', 1) AS prefix,
       split_part(id, '-', 2) AS number
FROM orders;

В песочнице идентификаторы короткие, ord-01: первый кусок ord, второй 01, третьего нет вовсе. Попросите третий, получите пустую строку, а не ошибку; на этом легко не заметить, что разбираете не тот формат. Где номер действительно составной, кусков больше: split_part('ord-2026-0042', '-', 2) даст 2026, а третий кусок 0042.

Когда разделителя нет, а есть фиксированные позиции, работает substring: substring(id FROM 5 FOR 4) это четыре символа начиная с пятого. Позицию подстроки находит position('-' IN id), край строки берут left(title, 20) и right(title, 4).

Длину считает length(title) по символам, а не по байтам, поэтому кириллица считается правильно: в слове «наушники» восемь символов, хотя в кодировке UTF-8 байтов шестнадцать; байты считает octet_length. Типичная задача витрины: обрезать длинное название и показать, что оно обрезано:

живой пример

SELECT id,
       CASE WHEN length(title) > 20 THEN left(title, 20) || '…' ELSE title END AS card_title
FROM products;

Собрать много строк в одну: string_agg

Обратная задача склейке: в отчёте по заказам нужна одна ячейка «что купили», а товары лежат отдельными строками в order_items. Их собирает агрегат string_agg, который работает вместе с GROUP BY, как sum или count, только складывает строки через разделитель:

живой пример

SELECT o.id, string_agg(p.title, ', ' ORDER BY p.title) AS items
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
GROUP BY o.id
ORDER BY o.id;

ORDER BY внутри скобок задаёт порядок кусков в ячейке; без него порядок случайный, и одинаковые заказы в двух выгрузках выглядят по-разному. NULL агрегат пропускает сам, как concat_ws. Обратно, из одной строки в много, разворачивает string_to_table или regexp_split_to_table; так разбирают список тегов, который кто-то сохранил одной строкой через запятую.

CASE: ветвление внутри запроса

В базе статусы заказа хранятся кодами: PENDING_PAYMENT, PAID, CANCELLED. Человеку нужен русский текст, и подставляет его CASE, единственная конструкция ветвления в SQL:

живой пример

SELECT id,
       CASE status
           WHEN 'PENDING_PAYMENT' THEN 'Ждёт оплаты'
           WHEN 'PAID'            THEN 'Оплачен'
           WHEN 'SHIPPED'         THEN 'Отправлен'
           WHEN 'DELIVERED'       THEN 'Доставлен'
           WHEN 'CANCELLED'       THEN 'Отменён'
           ELSE status
       END AS status_ru
FROM orders;

Ветка ELSE здесь не для красоты: когда в системе появится новый статус, строка покажет его код, а не пустоту, и пропажу будет видно сразу. Без ELSE CASE возвращает NULL, и новый статус молча превратится в пустую ячейку.

Тот же CASE умеет проверять условия, а не только равенство:

CASE WHEN total_amount >= 10000 THEN 'крупный'
     WHEN total_amount >= 1000  THEN 'обычный'
     ELSE 'мелкий' END AS bucket

Ветки проверяются сверху вниз, и срабатывает первая подошедшая, поэтому пороги пишут от большего к меньшему. В обратном порядке первая ветка забирает все заказы дороже тысячи, и «крупный» не сработает никогда, хотя написан без ошибок. CASE работает и в ORDER BY, и в GROUP BY, и внутри агрегатов: sum(CASE WHEN status = 'PAID' THEN total_amount ELSE 0 END) считает выручку только по оплаченным, не отфильтровывая остальные строки.

Коротко

• || возвращает NULL, если хоть один кусок NULL; concat_ws(' ', …) пропускает пустые и сам ставит разделители.
• Число в склейке приводят к строке: total_amount::text, для формата to_char, и шаблон берут с запасом.
• Текст от людей сравнивают приведённым с обеих сторон: lower(trim(x)); колонке типа citext это не нужно.
• ILIKE ищет без учёта регистра; % в начале шаблона отключает индекс; сам % в шаблоне экранируют.
• Чистка при записи: replace для одного знака, regexp_replace с флагом 'g' для класса символов.
• Разбор: split_part по разделителю, substring/left/right по позициям, length по символам; сборка обратно: string_agg с ORDER BY.
• CASE переводит коды в слова и считает по условию; ветки идут сверху вниз, ELSE не даёт новому коду превратиться в пустоту.

Что почитать дальше

• NULL и типы данных: где все спотыкаются — почему пустота заражает выражения и что с этим делает COALESCE.
• Строковые типы в PostgreSQL: text, varchar и char — что выбрать для колонки и чем опасен char.
• Типы индексов в PostgreSQL — триграммный индекс для поиска по подстроке.
• Полнотекстовый поиск — когда ILIKE перестаёт быть ответом.