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

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

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

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

Склейка: || и почему она вдруг возвращает пустоту

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

live example

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

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

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

SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name
FROM customer;

Числа в склейку сами не пойдут — их приводят к строке явно:

live example

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

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

Один и тот же текст, записанный по-разному

'Смирнова' и 'СМИРНОВА' для базы разные, и WHERE last_name = 'Смирнова' просто не найдёт вторую. Чтобы сравнивать по смыслу, обе стороны приводят к одному виду:

• lower(x) — всё строчными, upper(x) — всё прописными;
• initcap(x) — «Как Имя Собственное»: первая буква каждого слова прописная;
• trim(x) — убирает пробелы по краям (ltrim и rtrim — с одной стороны).

live example

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

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

Отдельная тонкость — тип char(n): он добивает строку пробелами до полной длины, и 'ок' в char(10) на самом деле хранится как 'ок '. Поэтому в новых таблицах используют text или varchar, а не char — подробности в статье Строковые типы в PostgreSQL.

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

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

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

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

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

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

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

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

live example

SELECT id,
       split_part(id, '-', 2) AS year,
       split_part(id, '-', 3) AS number
FROM orders;

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

Длину считает length(name) — по символам, а не по байтам, так что кириллица считается правильно. Типичная задача витрины — обрезать длинное название и показать, что оно обрезано:

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

Статус кодом, а на экране — словами

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

live example

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

Ветки проверяются сверху вниз, и срабатывает первая подошедшая — поэтому порядок от большего к меньшему, иначе всё уедет в первую же ветку.

Коротко

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

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

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