Строковые функции SQL — полный гайд с примерами

Коротко

Строковые функции преобразуют и анализируют текстовые данные: склеивают, обрезают, заменяют, приводят к единому регистру. Аналитику данных они нужны для чистки данных, парсинга UTM-меток, извлечения доменов из email, стандартизации названий. На собеседованиях по SQL строковые функции встречаются в задачах на обработку сырых данных — когда нужно привести хаос к порядку одним запросом.

CONCAT — склейка строк

CONCAT соединяет две и более строк в одну. Если один из аргументов NULL, CONCAT в PostgreSQL и MySQL заменит его на пустую строку (не вернёт NULL).

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users

В PostgreSQL чаще используют оператор ||:

SELECT first_name || ' ' || last_name AS full_name
FROM users

Разница: || вернёт NULL, если хотя бы один аргумент NULL. CONCAT — нет. На практике это важно, когда в данных есть пропуски.

CONCAT_WS — склейка с разделителем. Первый аргумент — разделитель, остальные — значения. Пропускает NULL-аргументы:

SELECT CONCAT_WS(', ', city, street, building) AS address
FROM clients
-- если street IS NULL, вернёт 'Москва, 12', а не 'Москва, , 12'

SUBSTRING / SUBSTR — извлечение подстроки

Вырезает часть строки по позиции и длине.

-- SUBSTRING(строка FROM начало FOR длина)
SELECT SUBSTRING(phone FROM 1 FOR 3) AS country_code
FROM users

-- альтернативный синтаксис
SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM users

Позиции в SQL начинаются с 1, а не с 0.

LENGTH / CHAR_LENGTH — длина строки

SELECT email, LENGTH(email) AS email_length
FROM users
WHERE LENGTH(email) > 50

LENGTH в PostgreSQL возвращает количество символов. В MySQL LENGTH возвращает количество байтов (для UTF-8 кириллица займёт 2 байта на символ), а CHAR_LENGTH — количество символов. На собеседовании могут уточнить разницу.

UPPER / LOWER — регистр

Приводят строку к верхнему или нижнему регистру. Базовый приём для стандартизации данных.

SELECT *
FROM users
WHERE LOWER(email) = 'ivan@example.com'

SELECT UPPER(city) AS city_upper
FROM clients

Частый кейс — поиск без учёта регистра. В PostgreSQL есть ILIKE (аналог LIKE, но регистронезависимый), но в MySQL и других СУБД приходится оборачивать в LOWER.

TRIM / LTRIM / RTRIM — удаление пробелов

TRIM убирает пробелы (или указанные символы) с обоих концов строки. LTRIM — слева, RTRIM — справа.

SELECT TRIM('  Москва  ') -- 'Москва'
SELECT LTRIM('  Москва  ') -- 'Москва  '
SELECT RTRIM('  Москва  ') -- '  Москва'

-- удаление конкретного символа (PostgreSQL)
SELECT TRIM(BOTH '/' FROM '/api/v1/') -- 'api/v1'

На практике TRIM спасает от невидимых пробелов в данных, которые ломают JOIN и группировки. Если GROUP BY city выдаёт отдельно «Москва» и «Москва » — это оно.

REPLACE — замена подстроки

Заменяет все вхождения подстроки на другую.

SELECT REPLACE(phone, '-', '') AS clean_phone
FROM users
-- '8-999-123-45-67' → '89991234567'

SELECT REPLACE(REPLACE(city, 'г.', ''), 'г ', '') AS clean_city
FROM clients

POSITION / STRPOS — поиск подстроки

Возвращает позицию первого вхождения подстроки. Если не найдена — 0.

-- PostgreSQL: POSITION(подстрока IN строка)
SELECT POSITION('@' IN email) AS at_pos
FROM users

-- PostgreSQL также: STRPOS(строка, подстрока)
SELECT STRPOS(url, '?') AS query_start
FROM page_views

LEFT / RIGHT — начало и конец строки

LEFT возвращает N символов с начала, RIGHT — с конца.

SELECT LEFT(phone, 1) AS first_digit
FROM users

SELECT RIGHT(card_number, 4) AS last_four_digits
FROM payments

SPLIT_PART — разбиение по разделителю (PostgreSQL)

Разбивает строку по разделителю и возвращает N-ю часть.

SELECT SPLIT_PART(email, '@', 2) AS domain
FROM users
-- 'ivan@gmail.com' → 'gmail.com'

SELECT SPLIT_PART(utm_campaign, '_', 1) AS campaign_type
FROM traffic
-- 'brand_search_msk' → 'brand'

В MySQL аналога нет — приходится комбинировать SUBSTRING и LOCATE.

INITCAP — каждое слово с заглавной (PostgreSQL)

SELECT INITCAP('иван петров') -- 'Иван Петров'
SELECT INITCAP('МОСКВА') -- 'Москва'

Полезно для стандартизации имён и городов. В MySQL эту функцию приходится эмулировать.

Практические примеры

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

SELECT
    SPLIT_PART(email, '@', 2) AS domain,
    COUNT(*) AS user_count
FROM users
GROUP BY SPLIT_PART(email, '@', 2)
ORDER BY user_count DESC

Чистка телефонных номеров

SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', ''), ')', '') AS clean_phone
FROM users

Парсинг UTM-параметров из URL

SELECT
    SPLIT_PART(
        SPLIT_PART(url, 'utm_source=', 2),
        '&', 1
    ) AS utm_source
FROM page_views
WHERE url LIKE '%utm_source=%'

Стандартизация названий городов

SELECT
    INITCAP(TRIM(LOWER(
        REPLACE(REPLACE(city, 'г.', ''), 'г ', '')
    ))) AS clean_city
FROM clients

PostgreSQL vs MySQL: ключевые различия

Функция	PostgreSQL	MySQL
Конкатенация	|| и CONCAT	CONCAT (оператор || не работает)
LENGTH	символы	байты (используйте CHAR_LENGTH)
SPLIT_PART	есть	нет (SUBSTRING_INDEX)
INITCAP	есть	нет
ILIKE	есть	нет (используйте LOWER + LIKE)
STRPOS	есть	нет (используйте LOCATE)

На собеседованиях чаще спрашивают PostgreSQL-синтаксис, но знать отличия полезно — могут уточнить.

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

Забыть про NULL. Оператор || в PostgreSQL вернёт NULL, если хотя бы один аргумент NULL. Используйте CONCAT или COALESCE.

LENGTH вместо CHAR_LENGTH в MySQL. Для кириллицы LENGTH('Москва') = 12 (байтов), а CHAR_LENGTH('Москва') = 6 (символов).

Индексация с 0. В SQL позиции начинаются с 1. SUBSTRING('abc', 0, 2) не даст ожидаемый результат — это не Python.

TRIM не помогает. Если после TRIM данные всё ещё не матчатся — проверьте на невидимые символы: табуляции, неразрывные пробелы (CHAR(160)). Используйте REPLACE(string, CHR(160), '').

Вопросы с собеседований

— Чем отличается CONCAT от оператора || в PostgreSQL? — CONCAT игнорирует NULL-аргументы (заменяет на пустую строку). Оператор || вернёт NULL, если хотя бы один операнд NULL. Поэтому 'Hello' || NULL = NULL, а CONCAT('Hello', NULL) = 'Hello'.

— Как извлечь домен из email одним запросом? — В PostgreSQL: SPLIT_PART(email, '@', 2). Универсальный способ: SUBSTRING(email FROM POSITION('@' IN email) + 1).

— Чем LENGTH отличается от CHAR_LENGTH? — В PostgreSQL оба возвращают количество символов. В MySQL LENGTH возвращает байты, а CHAR_LENGTH — символы. Для латиницы разницы нет, для кириллицы и других многобайтовых кодировок — есть.

— Как привести данные к единому регистру для корректного JOIN? — Оберните обе стороны в LOWER (или UPPER): WHERE LOWER(a.city) = LOWER(b.city). Лучше стандартизовать данные заранее, потому что функции на столбцах убивают использование индексов.

— Напишите запрос: вывести пользователей, у которых email на gmail.com, и первую букву имени — заглавную. — SELECT INITCAP(name) AS name, email FROM users WHERE email LIKE '%@gmail.com'. Или через LOWER: WHERE LOWER(email) LIKE '%@gmail.com' — чтобы поймать и 'GMAIL.COM'.

---

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

FAQ

Какие строковые функции спрашивают на собеседованиях чаще всего?

CONCAT, SUBSTRING, TRIM, LOWER/UPPER и REPLACE. Обычно не спрашивают отдельно «расскажите про TRIM» — дают задачу на чистку данных, где нужно скомбинировать несколько функций. Типичный формат: «напишите запрос, который из сырых данных извлечёт нужное поле».

Строковые функции работают с NULL?

Большинство строковых функций вернут NULL, если аргумент NULL. Исключение — CONCAT, который заменяет NULL на пустую строку. Если в данных есть пропуски, оборачивайте в COALESCE: LOWER(COALESCE(city, '')).

Влияют ли строковые функции на производительность?

Да. Функции на столбцах (LOWER(city), TRIM(name)) не дают использовать обычные индексы. Если фильтруете часто — создайте функциональный индекс: CREATE INDEX idx_users_email_lower ON users (LOWER(email)). Или стандартизуйте данные при вставке.

Как тренироваться

Строковые функции лучше всего учатся на задачах с реальными данными — чистка email-ов, парсинг URL, стандартизация имён. В тренажёре Карьерник есть задачи на обработку строк и другие темы SQL — с разборами. Больше вопросов — в разделе с примерами.