SQL

БЛОК 5. Строки и даты — 15. Работа со строками

📚 23 вопросовПройти тест →
Лекция

БЛОК 5. Строки и даты — 15. Работа со строками

SQL

15. Работа со строками

🧭 Введение: почему строки ломают отчёты чаще, чем кажется

Числа и даты обычно предсказуемы, а вот строки в базе часто «грязные»:
лишние пробелы, разный регистр, опечатки, тестовые значения, неполные данные.
Из-за этого фильтры и сегменты начинают вести себя странно:
похожие значения не совпадают, поиск ничего не находит, метрики расходятся.
Чтобы этого избежать, нужно уверенно владеть базовыми строковыми функциями:
CONCAT, LOWER/UPPER, LENGTH, TRIM, LIKE.
💡 Совет: Любую строковую задачу сначала формулируйте как 2 шага:
  1. нормализовать значение, 2) сравнить/показать его по правилу.
✅ Вывод: Работа со строками — это про качество данных и корректную фильтрацию, а не про «косметику запроса».

⚠️ Проблема -> решение

Типичная проблема:
  1. строки сравниваются «как есть»,
  2. не учитываются пробелы и регистр,
  3. поиск строится только на LIKE '%...%' без понимания последствий.
Последствия:
  1. потерянные записи в отчётах,
  2. некорректные сегменты для CRM,
  3. медленные запросы на больших таблицах.
Решение:
  1. нормализовать строки перед сравнением (TRIM, LOWER),
  2. выбирать правильный тип проверки (= или LIKE),
  3. контролировать NULL и пограничные случаи.
🟢 Если совсем просто: Сначала чистим строку, потом сравниваем, потом проверяем производительность.
🎯 Как понять, что этап прошёл успешно: Вы можете объяснить, почему строка попала или не попала в фильтр.

🛠️ Чем помогает и как работает

Строковые функции нужны почти в каждом прикладном SQL:
  • формирование полного имени/адреса;
  • нормализация email и тегов;
  • валидация длины поля;
  • поиск по части слова;
  • очистка импортированных данных.
🟢 Если совсем просто: Функции обработки строк приводят данные к единому виду, чтобы сравнение было честным.
🎯 Как понять, что этап прошёл успешно: Вы умеете последовательно применить TRIM -> LOWER -> сравнение.
Чем помогает:
  • CONCAT собирает текст из нескольких полей;
  • LOWER/UPPER решают проблему регистра;
  • LENGTH проверяет полноту/качество строки;
  • TRIM убирает лишние пробелы;
  • LIKE даёт шаблонный поиск.
Как это работает:
  • Шаг 1: выбираем поле и цель (показ, фильтр, проверка качества).
  • Шаг 2: нормализуем строку при необходимости.
  • Шаг 3: применяем сравнение или шаблон поиска.
  • Шаг 4: отдельно обрабатываем NULL.
  • Шаг 5: проверяем результат на реальных кейсах.
✅ Вывод: Базовые строковые функции закрывают большую часть практических задач по текстовым данным.

📚 Ключевые термины (простыми словами)

Перед практикой синхронизируем словарь.
🟢 Если совсем просто: Термины ниже помогают не путать «как показать строку» и «как правильно её сравнить».
🎯 Как понять, что этап прошёл успешно: Вы понимаете, какая функция нужна для сборки, очистки, длины и поиска.
  • Concatenation — склейка нескольких строк в одну.
  • Case normalization — приведение к единому регистру (LOWER/UPPER).
  • Length check — проверка длины строки (LENGTH).
  • Whitespace cleanup — очистка пробелов (TRIM).
  • Pattern matching — поиск по шаблону (LIKE).
  • Wildcard % — «любая последовательность символов».
  • Wildcard _ — «ровно один символ».
  • Case-insensitive search — поиск без учёта регистра (ILIKE в PostgreSQL).
✅ Вывод: Эти термины покрывают базовый словарь темы «Работа со строками».

🔗 1. CONCAT: собираем строки из полей

CONCAT используется, когда нужно собрать читабельный текст из нескольких колонок:
ФИО, полный адрес, подпись записи для UI.
🟢 Если совсем просто: CONCAT склеивает текстовые части в одну строку.
🎯 Как понять, что этап прошёл успешно: Вы получаете строку в нужном формате даже при частично пустых данных.
Назначение: Формировать вычисляемые текстовые поля без постобработки в приложении.
Простыми словами: Берём куски текста и соединяем их в заданном порядке.
Для новичка: Чаще удобнее явно добавлять разделители (' ', ', ', ' - '). В PostgreSQL CONCAT_WS(delim, ...) пропускает NULL и не добавляет лишние разделители.
Аналогия: Это как собрать подпись из имени, роли и города.
Пример:
SELECT  u.id,  CONCAT(u.last_name, ' ', u.first_name) AS full_name,  CONCAT('ID-', u.id) AS public_labelFROM users u;
Важно про NULL: В разных СУБД поведение склейки с NULL отличается.
Если хотите предсказуемый результат, используйте CONCAT_WS (PostgreSQL) или COALESCE.
Пример безопасной склейки:
SELECT  u.id,  CONCAT_WS(' ', u.last_name, u.first_name) AS full_name_pg,  CONCAT(COALESCE(u.last_name, ''), ' ', COALESCE(u.first_name, '')) AS full_name_portableFROM users u;
🔎 Как это происходит на практике:
  • Контекст: таблица пользователей в админке.
  • Действия: формируют full_name и короткую метку.
  • Результат: фронтенд получает готовые человекочитаемые поля.
Характеристики:
  • повышает читаемость API-ответа;
  • снижает объём ручной склейки на клиенте;
  • требует аккуратного обращения с NULL.
Когда использовать: Когда нужно собрать строку из нескольких текстовых компонентов.
✅ Вывод: CONCAT полезен для представления данных, но формат лучше фиксировать явно.

🔡 2. LOWER / UPPER: нормализация регистра

Регистр часто мешает корректным сравнениям:
User@Mail.com, user@mail.com, USER@MAIL.COM могут оказаться логически одним значением.
🟢 Если совсем просто: LOWER/UPPER приводят строку к одному регистру перед сравнением.
🎯 Как понять, что этап прошёл успешно: Поиск и фильтрация не зависят от случайного регистра ввода.
Назначение: Сделать текстовые сравнения стабильными.
Простыми словами: Сначала переводим строку в единый регистр, потом сравниваем.
Для новичка: Если это возможно, храните данные уже в нормализованном виде.
Аналогия: Это как сравнивать слова в словаре после перевода в нижний регистр.
Пример:
SELECT  u.id,  u.emailFROM users uWHERE LOWER(u.email) = LOWER('VIP@MAIL.COM');
🔎 Как это происходит на практике:
  • Контекст: поиск пользователя по email.
  • Действия: обе стороны сравнения приводят к LOWER.
  • Результат: поиск находит запись независимо от регистра.
Характеристики:
  • удобен для нормализации сравнения;
  • может влиять на индексный план при функции над колонкой;
  • если колонка уже хранится в нормализованном виде, нормализуйте входной параметр, а не колонку;
  • для частых LOWER(column) в PostgreSQL стоит делать функциональный индекс;
  • в PostgreSQL часто удобно использовать ILIKE для шаблонного case-insensitive поиска.
Пример production-подхода:
SELECT  u.id,  u.emailFROM users uWHERE u.email = LOWER(:email_param); CREATE INDEX idx_users_email_lower ON users (lower(email));
Когда использовать: Когда значения должны считаться одинаковыми независимо от регистра.
✅ Вывод: LOWER/UPPER решают проблему регистра, но архитектурно лучше хранить нормализованные данные.

📏 3. LENGTH: проверяем длину строки

LENGTH помогает валидировать качество данных:
слишком короткие логины, пустые имена, подозрительно длинные поля.
🟢 Если совсем просто: LENGTH возвращает количество символов в строке.
🎯 Как понять, что этап прошёл успешно: Вы можете найти и отфильтровать строки, не проходящие правила длины.
Назначение: Контроль качества и базовая валидация текстовых полей.
Простыми словами: Если длина не в допустимом диапазоне, запись нужно проверить.
Для новичка: Перед LENGTH часто полезно использовать TRIM, чтобы не считать лишние пробелы. В PostgreSQL LENGTH(text) считает символы, а OCTET_LENGTH(text) — байты.
Аналогия: Это как проверка минимальной длины пароля при регистрации.
Пример:
SELECT  u.id,  u.username,  LENGTH(TRIM(u.username)) AS username_lenFROM users uWHERE LENGTH(TRIM(u.username)) < 3;
Бонус для PostgreSQL:
SELECT  LENGTH('ёж') AS chars,  OCTET_LENGTH('ёж') AS bytes;
🔎 Как это происходит на практике:
  • Контекст: аудит качества импортированных аккаунтов.
  • Действия: проверяют длину username после очистки.
  • Результат: быстро находят проблемные значения.
Характеристики:
  • полезен для quality-check;
  • часто комбинируется с TRIM;
  • помогает строить правила валидации в SQL-слое.
Когда использовать: Когда нужно контролировать минимальную/максимальную длину текстового поля.
✅ Вывод: LENGTH — простой и эффективный инструмент контроля качества строковых данных.

✂️ 4. TRIM: убираем лишние пробелы

Лишние пробелы в начале/конце строки — одна из самых частых причин «непонятных» несовпадений.
TRIM нормализует значения перед сравнением и показом.
🟢 Если совсем просто: TRIM удаляет пробелы по краям строки.
🎯 Как понять, что этап прошёл успешно: Значения, отличающиеся только пробелами, больше не считаются разными.
Назначение: Очистить текст перед фильтрацией, сравнением и записью в отчёты.
Простыми словами: Убираем «мусор» вокруг строки, чтобы сравнение было корректным.
Для новичка: TRIM не удаляет пробелы в середине строки — только по краям. TRIM(NULL) возвращает NULL; в WHERE это даёт UNKNOWN, поэтому NULL проверяйте отдельно.
Аналогия: Это как удалить случайные отступы вокруг слова в документе.
Пример:
SELECT  u.id,  TRIM(u.email) AS clean_emailFROM users uWHERE u.email IS NOT NULL  AND TRIM(u.email) <> '';
🔎 Как это происходит на практике:
  • Контекст: импорт пользователей из CSV.
  • Действия: очищают строки перед сравнением и сегментацией.
  • Результат: исчезают ложные несовпадения.
Характеристики:
  • снижает количество «грязных» строк;
  • улучшает точность фильтров;
  • часто применяется вместе с LOWER.
Когда использовать: Когда источник данных может содержать случайные пробелы.
✅ Вывод: TRIM — базовая гигиена строк перед любой серьёзной фильтрацией.

🔍 5. LIKE: поиск по шаблону

LIKE нужен, когда ищем строки не по точному совпадению, а по шаблону:
префикс, суффикс или подстрока.
🟢 Если совсем просто: LIKE проверяет, соответствует ли строка шаблону с % и _.
🎯 Как понять, что этап прошёл успешно: Вы выбираете правильный шаблон и понимаете, сколько строк он потенциально затронет.
Назначение: Реализовать гибкий поиск по текстовым полям.
Простыми словами:
  • %abc% — содержит abc;
  • abc% — начинается с abc;
  • %abc — заканчивается на abc.
Для новичка: В PostgreSQL ILIKE даёт case-insensitive шаблонный поиск. Если нужно искать буквальные символы % и _, используйте ESCAPE.
Аналогия: Это как поиск слова в тексте, где известна только часть фразы.
Пример:
SELECT  u.id,  u.emailFROM users uWHERE u.email ILIKE '%@gmail.com';
Пример поиска спецсимвола:
SELECT  c.id,  c.commentFROM comments cWHERE c.comment LIKE '%\_%' ESCAPE '\';
🔎 Как это происходит на практике:
  • Контекст: сегмент пользователей по почтовому домену.
  • Действия: фильтруют по шаблону домена.
  • Результат: быстро получают нужный сегмент.
Характеристики:
  • гибко для поиска по части строки;
  • LIKE 'abc%' часто может использовать btree-индекс;
  • LIKE '%abc%' обычно не использует такой индекс и может требовать полный просмотр (нужны спец-индексы/FTS).
Когда использовать: Когда нужен частичный поиск, а не точное сравнение.
✅ Вывод: LIKE/ILIKE полезны для поиска, но шаблон нужно выбирать осознанно.

⚡ 6. Комбинируем функции в реальных фильтрах

В production строковые функции редко используются по одной.
Чаще нужен конвейер: очистка (TRIM) -> нормализация (LOWER) -> проверка (LIKE/=).
🟢 Если совсем просто: Сначала приводим строку к единому виду, потом сравниваем.
🎯 Как понять, что этап прошёл успешно: Фильтр стабильно работает на «грязных» данных из разных источников.
Назначение: Сделать строковые фильтры устойчивыми к реальным данным.
Простыми словами: Комбинация функций закрывает большинство проблем импорта и пользовательского ввода.
Для новичка: Старайтесь не перегружать WHERE огромными выражениями — выносите повторяемую логику в CTE.
Аналогия: Это как сначала помыть и отсортировать детали, а потом собирать механизм.
Пример:
WITH normalized_users AS (  SELECT    u.id,    TRIM(LOWER(u.email)) AS email_norm,    TRIM(u.first_name) AS first_name_norm,    TRIM(u.last_name) AS last_name_norm  FROM users u)SELECT  n.id,  CONCAT(n.last_name_norm, ' ', n.first_name_norm) AS full_name,  n.email_normFROM normalized_users nWHERE n.email_norm LIKE '%@company.com';
Короткий CASE для quality-check строк:
SELECT  u.id,  u.email,  CASE    WHEN u.email IS NULL OR TRIM(u.email) = '' THEN 'missing'    WHEN u.email NOT LIKE '%@%' THEN 'invalid'    ELSE 'ok'  END AS email_qualityFROM users u;
🔎 Как это происходит на практике:
  • Контекст: B2B-сегментация клиентов.
  • Действия: нормализуют поля один раз в CTE и фильтруют по домену.
  • Результат: меньше ошибок и более читаемый SQL.
Характеристики:
  • повышает надёжность фильтрации;
  • упрощает повторное использование логики;
  • делает код легче для ревью.
Когда использовать: Когда данные приходят из разных источников и требуют нормализации перед поиском.
✅ Вывод: Комбинация TRIM + LOWER + LIKE — базовый production-паттерн для строк.

🧷 7. NULL-safe сравнение строк (PostgreSQL)

Иногда по бизнес-правилу нужно считать NULL равным NULL.
Обычное = так не работает, поэтому для PostgreSQL есть отдельные операторы.
🟢 Если совсем просто: IS NOT DISTINCT FROM — это = с учётом NULL,
IS DISTINCT FROM — это <> с учётом NULL.
🎯 Как понять, что этап прошёл успешно: Вы можете явно управлять логикой сравнения, где NULL — это тоже значение по правилу бизнеса.
Назначение: Делать предсказуемые сравнения строк в nullable-полях.
Простыми словами: Если обе стороны NULL, IS NOT DISTINCT FROM вернёт TRUE.
Для новичка: Это особенно полезно в фильтрах и JOIN, где обычные сравнения с NULL дают UNKNOWN.
Аналогия: Это как сравнение с флажком «считать отсутствие значения осознанным совпадением».
Пример:
SELECT  u.id,  u.middle_nameFROM users uWHERE u.middle_name IS NOT DISTINCT FROM :middle_name_param;
🔎 Как это происходит на практике:
  • Контекст: фильтр по nullable-атрибуту в админке.
  • Действия: используют IS NOT DISTINCT FROM вместо =.
  • Результат: логика сравнения не ломается на NULL.
Характеристики:
  • убирает часть ошибок трёхзначной логики;
  • делает условие явно читаемым для ревью;
  • особенно полезен в PostgreSQL-проектах.
Когда использовать: Когда бизнес-правило требует корректного сравнения с учётом NULL.
✅ Вывод: IS [NOT] DISTINCT FROM — надёжный способ писать NULL-safe сравнения строк.

🆚 Сравнение: базовые строковые функции

ФункцияЧто делаетКогда полезнаРиск
CONCATСклеивает строкиФормирование display-полейНечёткий формат без разделителей
LOWER/UPPERНормализует регистрCase-insensitive сравненияФункция над колонкой может влиять на индекс
LENGTHСчитает длину строкиВалидация качестваНеверная проверка без TRIM
TRIMУдаляет пробелы по краямОчистка «грязных» данныхНе чистит пробелы внутри строки
LIKE/ILIKEПоиск по шаблонуЧастичный текстовый поиск%...% может быть дорогим
✅ Вывод: Выбор функции зависит от цели: представить, нормализовать, проверить или найти.

🧠 Must-Know (запомнить)

  • Перед сравнением строк полезно делать TRIM и нормализацию регистра.
  • CONCAT удобен для display-полей, но учитывайте NULL: в PostgreSQL полезен CONCAT_WS, для кросс-СУБД — COALESCE.
  • LENGTH лучше считать после TRIM, если важна «чистая» длина.
  • В PostgreSQL LENGTH — символы, OCTET_LENGTH — байты.
  • LIKE 'abc%' и LIKE '%abc%' сильно отличаются по стоимости и по использованию индексов.
  • В PostgreSQL для case-insensitive поиска по шаблону есть ILIKE.
  • Для буквального поиска % и _ в LIKE используйте ESCAPE.
  • При работе со строками всегда учитывайте NULL.
  • Для NULL-safe сравнения в PostgreSQL используйте IS NOT DISTINCT FROM.
  • Сложную повторяемую нормализацию удобнее выносить в CTE.
✅ Вывод: Эти правила закрывают основной набор практических задач по строкам в SQL.

❌ Частые мифы

❌ Миф: LIKE всегда подходит для любого поиска. ✅ Как правильно: LIKE полезен для шаблонов, но точные сравнения лучше делать через =. 📎 Почему это важно: иначе запросы становятся тяжелее и менее предсказуемыми.
❌ Миф: TRIM и LOWER нужны только для «красоты». ✅ Как правильно: это базовая нормализация, влияющая на корректность фильтров. 📎 Почему это важно: без нормализации легко потерять нужные строки.
❌ Миф: CASE не нужен в теме строк. ✅ Как правильно: CASE часто помогает классифицировать строки по качеству/шаблону. 📎 Почему это важно: это мост к реальным аналитическим и API-задачам.
❌ Миф: строковые функции почти не влияют на производительность. ✅ Как правильно: неудачное применение функций в фильтрах может сделать запрос заметно медленнее. 📎 Почему это важно: на больших таблицах это напрямую влияет на SLA.

🎤 Часто спрашивают на собеседованиях

❓ Вопрос: Когда использовать CONCAT, а когда хранить поле отдельно? ✅ Ответ: CONCAT используйте для вычисляемого представления, исходные компоненты храните отдельно.
❓ Вопрос: Зачем делать LOWER() при сравнении email? ✅ Ответ: чтобы убрать зависимость от регистра и не терять логически одинаковые значения.
❓ Вопрос: Чем LENGTH полезен в реальной задаче? ✅ Ответ: помогает валидировать качество данных и находить подозрительно короткие/длинные значения.
❓ Вопрос: Что делает TRIM и чего он не делает? ✅ Ответ: удаляет пробелы по краям строки, но не убирает пробелы внутри строки.
❓ Вопрос: В чём разница между LIKE и ILIKE в PostgreSQL? ✅ Ответ: LIKE чувствителен к регистру, ILIKE — нет.
❓ Вопрос: Почему LIKE '%abc%' может быть медленным? ✅ Ответ: такой шаблон часто требует просмотра большого числа строк, особенно без подходящих индексов.

🚨 Типичные ошибки

  • Сравнивать «грязные» строки без TRIM.
  • Путать точное сравнение (=) и поиск по шаблону (LIKE).
  • Применять LOWER() только к одной стороне сравнения.
  • Забывать про NULL в строковых фильтрах.
  • Использовать %...% без оценки стоимости на больших таблицах.
  • Считать длину строки без предварительной очистки пробелов.
  • Дублировать одну и ту же строковую нормализацию в нескольких местах запроса.
✅ Вывод: Большинство проблем со строками решается дисциплиной нормализации и осознанным выбором фильтра.

✅ Best Practices

  • Нормализуйте строку перед сравнением: TRIM + LOWER.
  • Отделяйте display-логику (CONCAT) от логики фильтрации.
  • Используйте LENGTH для quality-check и валидации.
  • Выбирайте шаблон LIKE осознанно и минимально необходимый.
  • Для повторяемой нормализации выносите логику в CTE.
  • Проверяйте граничные кейсы: пустая строка, пробелы, NULL.
  • Для тяжёлых строковых фильтров смотрите EXPLAIN.
✅ Вывод: Хорошая работа со строками в SQL — это баланс корректности, читаемости и производительности.

🏁 Заключение

Строковые функции — фундамент повседневной SQL-практики:
они нужны и в фильтрации, и в подготовке данных для API, и в аналитике.
Если вы уверенно используете CONCAT, LOWER/UPPER, LENGTH, TRIM, LIKE,
то контролируете и качество данных, и предсказуемость результата.
✅ Вывод: Сильный инженер по SQL умеет не только считать, но и грамотно «готовить» текстовые данные.
🎯

Проверьте знания

Закрепите материал — пройдите тест по теме «БЛОК 5. Строки и даты — 15. Работа со строками»

Пройти тест →