Строковые функции SQL: SUBSTRING, CONCAT, REPLACE и TRIM на примерах

Коротко

Если текстовая колонка идёт в GROUP BY, JOIN или фильтр, её сначала нормализуют, а уже потом считают. Большинство ошибок со строками не роняют запрос, а тихо делят одну категорию на несколько или теряют строки из-за NULL.

• lower(trim(col)) перед группировкой — минимальная гигиена для категорий из разных источников.
• trim убирает только обычные пробелы: табуляция и неразрывный пробел остаются.
• substring, left, right режут по позиции, split_part — по разделителю; второе надёжнее, когда длина частей разная.
• concat пропускает NULL, оператор || превращает всю строку в NULL. В MySQL CONCAT ведёт себя как ||.
• Функция от колонки в WHERE мешает обычному индексу в PostgreSQL. Нужен индекс по выражению или нормализация при загрузке.
• Регистр кириллицы и порядок сортировки зависят от collation базы, а не от самого SQL.

Шпаргалка по строковым функциям SQL

Результаты в таблице получены в DuckDB. Столбец с различиями описывает только то, что точно отличается между PostgreSQL, DuckDB и MySQL; если различий нет, это тоже сказано.

LOWER, UPPER и TRIM: нормализация категорий перед GROUP BY

В учебной базе каналы записаны аккуратно: четыре значения без лишнего регистра и пробелов. Чтобы показать ситуацию из вступления, грязные значения собраны прямо в запросе через VALUES. Суммы подобраны так, чтобы после чистки совпасть с реальной базой: 1747 пользователей в organic и 1015 в paid_search.

Без нормализации GROUP BY видит семь каналов. lower и trim сводят их к трём: остаётся paid search с пробелом внутри. Его добивает replace, и каналов становится два — столько, сколько их на самом деле.

with raw(channel, users) as (
  values ('paid_search', 812), ('Paid_Search', 121), ('paid_search ', 53),
         ('paid search', 29), ('organic', 1402), ('Organic', 318), (' organic', 27)
)
select
  replace(lower(trim(channel)), ' ', '_') as channel,
  sum(users)                             as users,
  count(*)                               as raw_variants
from raw
group by 1
order by users desc;

-- channel      users  raw_variants
-- organic      1747   3
-- paid_search  1015   4

Сколько вариантов склеила нормализация

Перед тем как заменить сырую колонку чистой, посчитайте, сколько различных значений было до и после каждого шага. Это одна строка результата, и она сразу показывает масштаб проблемы: 7 сырых значений, 3 после lower и trim, 2 после replace.

Тот же запрос полезно держать как проверку качества на реальной таблице. На учебной базе в users.channel, payments.plan и events.event_name нет ни одного значения, которое отличалось бы от lower(trim(...)). В рабочих данных, собранных из нескольких систем, такая чистота — редкость.

upper нужен реже — обычно для кодов, которые принято писать заглавными: страны, валюты, тикеры. Правило то же: один регистр для всех значений до группировки.

with raw(channel, users) as (
  values ('paid_search', 812), ('Paid_Search', 121), ('paid_search ', 53),
         ('paid search', 29), ('organic', 1402), ('Organic', 318), (' organic', 27)
)
select
  count(distinct channel)                                   as raw_values,   -- 7
  count(distinct lower(trim(channel)))                      as lower_trim,   -- 3
  count(distinct replace(lower(trim(channel)), ' ', '_'))   as clean_values  -- 2
from raw;

TRIM не видит табуляцию и неразрывный пробел

По умолчанию trim снимает с краёв только обычный пробел. В DuckDB trim(chr(9) || 'organic' || chr(10)) оставляет строку длиной 9 символов: табуляция и перевод строки на месте. Неразрывный пробел chr(160), который приходит из Excel и веб-форм, trim тоже не трогает. В PostgreSQL trim без аргументов по умолчанию снимает тоже только пробел.

Для пробельных символов внутри и по краям надёжнее регулярное выражение: схлопнуть любую последовательность в один пробел и только потом применить trim. Неразрывный пробел лучше заменить явно через replace(col, chr(160), ' '): класс \s в регулярных выражениях DuckDB его не включает.

select trim(regexp_replace('  paid' || chr(9) || '  search ' || chr(10), '\s+', ' ', 'g')) as channel;

-- channel
-- paid search

LENGTH и CHAR_LENGTH: длина строки в SQL

length удобен как детектор невидимого мусора. Если длина значения не совпадает с длиной того же значения после trim, в нём есть пробелы по краям. Скобки в выводе делают их видимыми.

С кириллицей есть ловушка. В PostgreSQL и DuckDB length('Привет') и char_length('Привет') возвращают 6 — число символов. В MySQL LENGTH считает байты и вернёт 12, потому что в UTF-8 каждая кириллическая буква занимает два байта; число символов там даёт CHAR_LENGTH. В DuckDB байты считает strlen: для того же слова он вернёт 12. Если проверка вида «название не длиннее 50 символов» переезжает из MySQL в другую базу, результат поменяется.

with raw(channel) as (
  values ('paid_search'), ('Paid_Search'), ('paid_search '), ('paid search'),
         ('organic'), ('Organic'), (' organic')
)
select '[' || channel || ']' as shown,
       length(channel)       as len,
       length(trim(channel)) as len_trim
from raw
where length(channel) <> length(trim(channel));

-- shown           len  len_trim
-- [paid_search ]  12   11
-- [ organic]      8    7

REPLACE: замена подстроки в SQL

replace(строка, что, на_что) меняет все вхождения подстроки, без шаблонов и регистровых поблажек. Помимо чистки категорий, она чаще всего нужна, когда число пришло строкой: '1 290,50 ₽' из выгрузки бухгалтерии.

Цепочка из трёх replace убирает пробел и знак рубля и меняет запятую на точку, после чего строка приводится к decimal. Но если вместо обычного пробела в числе стоит неразрывный, первая замена его пропустит, и try_cast молча вернёт NULL. Такая строка выпадет из суммы без ошибки. Сначала уберите chr(160), затем остальное, и посчитайте строки, которые не привелись, — подробнее о try_cast и приведении типов в статье про CAST.

select
  try_cast(replace(replace(replace('1 290,50 ₽', ' ', ''), '₽', ''), ',', '.')
           as decimal(12, 2)) as plain_space,     -- 1290.50
  try_cast(replace(replace(replace('1' || chr(160) || '290,50 ₽', ' ', ''), '₽', ''), ',', '.')
           as decimal(12, 2)) as nbsp_missed,     -- NULL
  try_cast(replace(replace(replace(replace('1' || chr(160) || '290,50 ₽', chr(160), ''), ' ', ''), '₽', ''), ',', '.')
           as decimal(12, 2)) as nbsp_fixed;      -- 1290.50

SUBSTRING: часть строки по позиции и по шаблону

substring(строка, начало, длина) отсчитывает позиции с единицы. Без третьего аргумента берёт всё до конца строки. Запись substring(строка from 1 for 9) — стандартный синтаксис того же самого, её понимают PostgreSQL, DuckDB и MySQL.

Частое применение в аналитике — ключ месяца из даты: первые семь символов 2026-07-15 дают 2026-07. На учебной базе так получаются три месяца платежей: июнь — 167 платежей, июль — 442, август — 642. Работает, но это обходной путь: результат — строка, а не дата, и график по ней не знает про пропущенные месяцы. Для периодов лучше date_trunc, о нём в статье про функции даты.

Вырезать по шаблону PostgreSQL умеет той же функцией: substring(col from 'utm_source=([a-z]+)') вернёт первую захваченную группу. В DuckDB такой формы нет — запрос упадёт с ошибкой приведения шаблона к числу. Там используется regexp_extract(col, 'utm_source=([a-z]+)', 1), в MySQL — REGEXP_SUBSTR.

select substring(cast(paid_at as varchar), 1, 7) as month,
       count(*)                                  as payments
from payments
group by 1
order by 1;

-- month    payments
-- 2026-06  167
-- 2026-07  442
-- 2026-08  642

POSITION и STRPOS: где в строке стоит символ

strpos(строка, подстрока) и стандартная форма position(подстрока in строка) возвращают номер первого вхождения. В app_open подчёркивание стоит на четвёртой позиции, в workspace_created — на десятой. Если подстроки нет, результат 0, а не NULL.

Сама по себе позиция нужна редко. Её используют, чтобы резать строку переменной длины: всё до разделителя и всё после. В MySQL strpos нет, там POSITION, LOCATE или INSTR.

select distinct
  event_name,
  strpos(event_name, '_')                                 as pos,
  left(event_name, strpos(event_name, '_') - 1)           as object,
  substring(event_name, strpos(event_name, '_') + 1)      as action
from events
order by event_name;

-- app_open           4   app        open
-- export_completed   7   export     completed
-- invite_sent        7   invite     sent
-- report_created     7   report     created
-- workspace_created  10  workspace  created

LEFT и RIGHT: первые и последние символы

left(строка, n) и right(строка, n) — короткая запись substring с начала и с конца. У связки left + strpos есть неприятный край. Если в значении нет разделителя, strpos вернёт 0, left('signup', 0 - 1) получит отрицательную длину и в PostgreSQL и DuckDB вернёт строку без последнего символа: signu. Ошибки не будет, будет новая «категория».

right удобен для проверки суффикса. right(event_name, 8) = '_created' находит 4525 событий создания — столько же, сколько like '%_created'. Совпадение здесь случайное: в LIKE подчёркивание означает любой символ, и 'recreated' like '%_created' тоже истинно. Экранирование и другие тонкости шаблонов разобраны в статье про LIKE и ILIKE.

SPLIT_PART: часть строки по разделителю

split_part(строка, разделитель, n) делит строку и возвращает n-й кусок. Для событий вида объект_действие это самый короткий способ получить действие без подсчёта позиций. Если кусков меньше, чем n, функция вернёт пустую строку, а не NULL: split_part('signup', '_', 2) даёт ''. Это безопаснее связки left + strpos, но пустую строку в отчёте всё равно стоит заменить на понятную метку.

Отрицательный номер считает с конца: split_part('a_b', '_', -1) вернёт b в DuckDB и в PostgreSQL начиная с 14-й версии. В MySQL функции нет; ближайший аналог — SUBSTRING_INDEX(строка, разделитель, n), который возвращает всё до n-го разделителя, а не сам кусок.

select split_part(event_name, '_', 2) as action,
       count(*)                        as events,
       count(distinct user_id)         as users
from events
group by 1
order by events desc;

-- action     events  users
-- open       28693   4613
-- created    4525    2750
-- sent       1135    1135
-- completed  988     988

Домен из email: SPLIT_PART вместе с LOWER и TRIM

Классическая задача — разбить пользователей по почтовому домену, чтобы отделить корпоративные адреса или тестовые аккаунты. В учебной базе email нет, поэтому адреса заданы в запросе. Без нормализации доменов пять: YANDEX.RU и yandex.ru считаются разными, а у Gmail.com в конце пробел. После lower и trim доменов четыре.

Внутренний домен casepraktika.ru в такой разбивке сразу виден как отдельная строка. Дальше его обычно исключают из метрик тем же выражением в WHERE.

with emails(email) as (
  values ('anna.k@yandex.ru'), ('Ivan.Petrov@Gmail.com '), ('o.smirnova@yandex.ru'),
         ('petrov@mail.ru'), ('qa+1@casepraktika.ru'), ('m.orlova@YANDEX.RU')
)
select lower(trim(split_part(email, '@', 2))) as domain,
       count(*)                               as users
from emails
group by 1
order by users desc, domain;

-- yandex.ru        3
-- casepraktika.ru  1
-- gmail.com        1
-- mail.ru          1

CONCAT и ||: склеить строки и не потерять NULL

Склеить строки в SQL можно функцией concat или оператором ||. Разница видна только на NULL, и на учебной базе она заметна: у 55 из 4613 пользователей не заполнена страна. Сегмент channel || ' / ' || country для них целиком превращается в NULL, и в группировке все 55 человек из четырёх каналов сваливаются в одну пустую строку. concat пропускает NULL и даёт partner / с висящим разделителем.

concat_ws(разделитель, ...) тоже пропускает NULL, но вместе с разделителем: получится просто partner. В отчёте такое значение легко принять за отдельный сегмент. Честнее всего явно подставить метку через coalesce — тогда 55 человек видны как unknown внутри своих каналов.

В PostgreSQL поведение то же, что в DuckDB: concat игнорирует NULL, || возвращает NULL. В MySQL наоборот: CONCAT возвращает NULL, если хоть один аргумент NULL, а || по умолчанию означает логическое OR и склеивает строки только с режимом PIPES_AS_CONCAT. Запрос с ||, перенесённый в MySQL, не упадёт, а вернёт нули и единицы.

select
  count(*)                                    as users,           -- 4613
  count(channel || ' / ' || country)          as pipe_not_null,   -- 4558
  count(concat(channel, ' / ', country))      as concat_not_null  -- 4613
from users;

select concat(channel, ' / ', coalesce(country, 'unknown')) as segment,
       count(*)                                             as users
from users
group by 1
order by users desc;

-- organic / RU           1015
-- referral / RU          614
-- ...
-- organic / unknown      22
-- paid_search / unknown  15
-- referral / unknown     10
-- partner / unknown      8

LPAD и RPAD: идентификаторы фиксированной длины

lpad(строка, длина, символ) дополняет строку слева до нужной длины. Типичная задача — показать числовой user_id так, как его видит поддержка или CRM: U000001 вместо 1. Число сначала приводится к строке.

Ловушка в обратную сторону: если строка длиннее заданной длины, lpad её обрежет. lpad('1234567', 6, '0') вернёт 123456 — так ведут себя DuckDB, PostgreSQL и MySQL. Когда пользователей станет больше миллиона, два разных id получат одинаковый публичный номер. Длину выбирайте с запасом или проверяйте максимум перед выгрузкой.

select user_id,
       'U' || lpad(cast(user_id as varchar), 6, '0') as public_id
from users
order by user_id
limit 3;

-- 1  U000001
-- 2  U000002
-- 3  U000003

STRING_AGG: собрать значения в одну строку

Обратная к split_part задача — собрать несколько строк в одну: путь пользователя по событиям, список тарифов клиента, перечень вариантов эксперимента. Это агрегатная функция string_agg(значение, разделитель order by ...). На учебной базе string_agg(distinct event_name, ', ' order by event_name) возвращает все пять событий через запятую. В MySQL ту же работу делает GROUP_CONCAT. Порядок внутри строки, дедупликация и длинные пути разобраны в отдельной статье про STRING_AGG.

Регулярные выражения: regexp_replace, regexp_matches и regexp_extract

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

• regexp_replace в PostgreSQL и DuckDB без флага 'g' меняет только первое совпадение: regexp_replace('a1b22c333', '[0-9]+', '#') даёт a#b22c333, с флагом — a#b#c#. MySQL заменяет все совпадения по умолчанию.
• regexp_matches в DuckDB возвращает boolean и подходит для фильтра: _(created|sent)$ находит 5660 событий. В PostgreSQL функция с тем же именем возвращает массивы захваченных групп, а проверку делают оператором ~ или regexp_like (с версии 15).
• Извлечь группу: в DuckDB regexp_extract(col, шаблон, 1), в PostgreSQL substring(col from шаблон) или regexp_substr (с версии 15), в MySQL REGEXP_SUBSTR. На учебной базе regexp_extract(event_name, '_([a-z]+)$', 1) даёт те же четыре действия, что и split_part.

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

В аналитическом DuckDB индексов почти не касаются, но запросы часто переезжают в продовую PostgreSQL. Там условие where lower(email) = 'anna.k@yandex.ru' не использует обычный B-tree индекс по email: индекс построен по исходным значениям, а сравнивается результат функции. На большой таблице это полное сканирование.

Выходов три. Нормализовать значение при загрузке и хранить уже чистым. Построить индекс по выражению — create index on users (lower(email)) — и писать в запросе ровно то же выражение. Или использовать тип citext, который сравнивает без учёта регистра. ILIKE с обычным B-tree индексом, как правило, тоже не работает; для поиска по подстроке без учёта регистра нужен триграммный индекс pg_trgm. Подробности — в статье про LIKE и ILIKE.

create index users_email_lower_idx on users (lower(email));

select user_id
from users
where lower(email) = 'anna.k@yandex.ru';  -- индекс подходит: выражение совпадает

Кириллица, регистр и collation

В DuckDB lower('ПЛАТНЫЙ ПОИСК Ёж') возвращает платный поиск ёж, а upper('ёжик') — ЁЖИК. В PostgreSQL результат зависит от локали базы: lower работает по правилам LC_CTYPE, и при локали C меняет регистр только у латиницы. Узнать настройку можно командой show lc_ctype.

Сортировка тоже зависит от collation. В DuckDB по умолчанию строки сравниваются побайтово, и список яблоко, ёж, ель, Жук сортируется как Жук, ель, яблоко, ёж: заглавные идут раньше строчных, а ё оказывается после я. Для отчёта с русскими названиями порядок лучше задавать явно или использовать нужный collation.

MySQL сравнивает строки по умолчанию без учёта регистра: collation с суффиксом _ci. Там 'Paid_Search' = 'paid_search' истинно, и GROUP BY сам склеит варианты, различающиеся регистром. В PostgreSQL и DuckDB это разные строки, поэтому один и тот же запрос по каналам в разных базах вернёт разное число строк. Отдельно помните про ё и е: для базы это разные буквы, и ёлка не найдётся по елка.

Частые ошибки со строками в SQL

Почти все они проходят без сообщения об ошибке. Их находят сверкой числа различных значений до и после чистки или суммы по сегментам с общим итогом.

• Группировка по сырой текстовой колонке из нескольких источников: одна категория делится на несколько.
• trim вместо полной чистки: табуляция и неразрывный пробел остаются в значении.
• || с колонкой, где бывают NULL: сегмент целиком превращается в NULL.
• left(col, strpos(col, '_') - 1) без проверки, что разделитель есть: signup превращается в signu.
• lpad с маленькой длиной: разные id обрезаются до одинакового номера.
• regexp_replace без флага g в PostgreSQL и DuckDB: заменено только первое совпадение.
• lower(col) в WHERE на большой таблице PostgreSQL без индекса по выражению.
• Перенос запроса в MySQL: LENGTH считает байты, CONCAT возвращает NULL, || становится логическим OR.

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

Все запросы из статьи, кроме примеров PostgreSQL с индексом, можно выполнить в песочнице SQL-курса: там та же учебная база и тот же DuckDB. Попробуйте разбить события по объекту, а не по действию, и собрать сегмент «канал / устройство / страна» так, чтобы ни один из 4613 пользователей не потерялся.