SQL с нуля. Часть 9: Строки и текст — LIKE, ILIKE, SUBSTRING, regex

Это часть 9 из 10 учебника «SQL с нуля для аналитика». Содержание серии в конце поста. ← Часть 8

TL;DR: LIKE 'pat%' — поиск по подстроке с wildcard (% любая последовательность, _ любой 1 символ). ILIKE — то же без учёта регистра. SUBSTRING(s FROM x FOR y) — обрезка. CONCAT или || — соединение. REGEXP_REPLACE / ~ — regex. Для analytics нужны почти все эти функции.

В этой части:

• LIKE, ILIKE и wildcard
• SUBSTRING и POSITION
• CONCAT, LENGTH, TRIM, UPPER, LOWER
• Регулярные выражения (regex)
• Разделение строк

Как искать по подстроке?

• % — любая последовательность (включая пустую)
• _ — ровно один любой символ

-- Email на yandex.ru
SELECT * FROM users WHERE email LIKE '%@yandex.ru';

-- Имена начинающиеся с 'A'
SELECT * FROM users WHERE first_name LIKE 'A%';

-- Имена из 4 букв
SELECT * FROM users WHERE first_name LIKE '____';

-- Любая буква, потом 'van' (Иван, Aван)
SELECT * FROM users WHERE first_name LIKE '_van';

Регистр имеет значение: LIKE 'a%' найдёт 'apple' но не 'Apple'. Для регистронезависимого:

SELECT * FROM users WHERE first_name ILIKE 'a%';   -- PG
SELECT * FROM users WHERE LOWER(first_name) LIKE 'a%';  -- универсально

Типичный кейс: бизнес попросил «всех клиентов из крупных компаний». Через WHERE email LIKE '%@gmail.com' OR email LIKE '%@yandex.ru' ничего не нашли — корпоративные клиенты с personal-доменами. Правильнее — лукап по domain через SPLIT_PART(email, '@', 2) IN (...).

Как обрезать или выделить подстроку?

-- Извлечь домен из email
SELECT
    email,
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- Первые 3 символа
SELECT SUBSTRING(name FROM 1 FOR 3) FROM users;

В ClickHouse: substring(s, start, length) — короче синтаксис.

Какие 8 основных строковых функций?

Какие типичные задачи аналитика?

Извлечь домен из email

SELECT email, SPLIT_PART(email, '@', 2) AS domain FROM users;

Нормализовать имя (убрать пробелы, в нижний регистр)

SELECT LOWER(TRIM(first_name)) AS normalized_name FROM users;

Соединить имя и фамилию

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- или
SELECT first_name || ' ' || last_name FROM users;

Фильтр по части телефона

SELECT * FROM users WHERE phone LIKE '+7%';

Поиск по нескольким маскам

SELECT * FROM products
WHERE name ILIKE '%iphone%' OR name ILIKE '%samsung%';

Типичная задача: «нормализация имён продуктов». LOWER(TRIM(REPLACE(name, ' ', ' '))) приводит " iPhone 15 Pro " → "iphone 15 pro". Дальше можно сравнивать без false-mismatch.

Что такое regex и когда его использовать?

Регулярные выражения — для сложных текстовых паттернов, недоступных LIKE.

В PG — оператор ~ (match) и !~ (не match), регистронезависимые ~* / !~*:

-- Email валидный (упрощённо)
SELECT * FROM users
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Имя начинается с заглавной кириллицы
SELECT * FROM users
WHERE first_name ~ '^[А-Я][а-я]+$';

-- Извлечь регион из адреса (например, 'XYZ region 12345' → '12345')
SELECT REGEXP_REPLACE(address, '.*\s(\d+)$', '\1') FROM customers;

Полезные regex-функции PG:

• REGEXP_REPLACE(s, pattern, replacement)
• REGEXP_MATCH(s, pattern) — возвращает массив групп
• REGEXP_SPLIT_TO_TABLE(s, pattern) — разбить на строки

Какие 4 ошибки со строками?

• Ошибка 1: LIKE без wildcard — это просто =. LIKE 'apple' найдёт только точное совпадение.
• Ошибка 2: Regex с экранированием — '\.' в SQL может потребовать '\\.' (двойной слеш).
• Ошибка 3: CONCAT(NULL, 'hello') в стандарте даёт NULL, в PG — 'hello' (NULL ignored). Знай свой диалект.
• Ошибка 4: TRIM убирает только пробелы. Для невидимых символов (tab, newline) — TRIM(BOTH E' \t\n' FROM s).

Частые вопросы про строки

LIKE или regex — что быстрее?

LIKE быстрее на простых паттернах ('%text%'). Regex быстрее когда заменяет 5+ OR-условий с LIKE.

Как искать без учёта регистра в MySQL?

TEXT или VARCHAR — что брать в PG?

В PG нет разницы по performance. TEXT короче в DDL. Используй TEXT.

Можно ли регулярки в ClickHouse?

Да: match(s, pattern), replaceRegexpOne(s, pat, repl). RE2-диалект.

Как считать символы в UTF-8?

Что дальше?

В Части 10 — финальная часть. Window Functions intro: ROW_NUMBER, RANK, LAG, LEAD. Самые частые на собесах и в реальной работе.

В Pro — безлимит мок-собесов на AI-интервью + 491 SQL-задача + 612 тестовых заданий + 50+ блог-постов.

Навигация по учебнику

← Часть 8 | Часть 9: Строки и текст | Часть 10 →

Содержание серии: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9 · 10

← Вернуться к оглавлению

Источники

• PostgreSQL Docs: «String Functions» (postgresql.org/docs/current/functions-string.html)
• PostgreSQL Docs: «Pattern Matching» (postgresql.org/docs/current/functions-matching.html)