Kitabı oxu: «Строки в SQL. 18 задач для чистки данных»

Перед первой задачей

Почему строки портят отчёты

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

Такие мелочи ломают группировку и соединения: один и тот же email превращается в две строки, а одинаковые товары не склеиваются по ключу.

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

Что важно помнить

Строковые функции зависят от СУБД сильнее, чем базовый SELECT. Примеры здесь запускаются в SQLite, а при переносе в другую систему нужно сверить названия функций.

Нормализация не должна прятать плохие данные. Если строка стала красивой после replace, всё равно полезно знать, сколько таких исправлений было.

Перед автоматической чисткой сначала сделайте маленький отчёт: исходное значение, нормализованное значение и причина изменения.

Маршрут по задачам

1. Убрать пробелы вокруг email

2. Привести email к нижнему регистру

3. Найти email с символом @

4. Очистить телефон от пробелов и дефисов

5. Получить префикс артикула

6. Найти позицию разделителя в артикуле

7. Проверить длину промокода

8. Вытащить домен из email

9. Сгруппировать email по доменам

10. Заменить русские пробелы в тегах

11. Собрать полное имя без двойных пробелов

12. Найти строки с лишними пробелами

13. Выделить код до первого дефиса

14. Посчитать строки с запрещённой меткой

15. Нормализовать город для группировки

16. Сделать короткий заголовок

17. Проверить формат внутреннего кода

18. Собрать финальный отчёт чистки строк

Практические задачи

Задача 1. Убрать пробелы вокруг email

Рабочий вопрос

Нужно привести email к аккуратному виду, если при вводе пользователь оставил пробелы в начале или конце строки.

Данные

CREATE TABLE clients(id INTEGER, email TEXT);

INSERT INTO clients VALUES

(1,' anna@example.test '),

(2,'oleg@example.test'),

(3,' nina@example.test');

Запрос

SELECT id, trim(email) AS clean_email

FROM clients

ORDER BY id;

Ожидаемый результат

[[1, "anna@example.test"], [2, "oleg@example.test"], [3, "nina@example.test"]]

Почему работает

trim(email) убирает пробелы по краям строки.

Внутренняя часть email не меняется.

ORDER BY сохраняет понятный порядок клиентов.

Где легко ошибиться

trim не удаляет пробелы внутри строки. Если пробел стоит в середине email, нужна отдельная проверка качества.

Самостоятельная проверка

Почему клиент 2 не изменился после trim, хотя функция применена ко всем строкам?

Ответ: У клиента 2 уже не было пробелов по краям email, поэтому результат совпал с исходной строкой.

Задача 2. Привести email к нижнему регистру

Рабочий вопрос

Нужно убрать различия регистра, чтобы одинаковые email не считались разными клиентами.

Данные

CREATE TABLE clients(id INTEGER, email TEXT);

INSERT INTO clients VALUES

(1,'Anna@Example.Test'),

(2,'oleg@example.test'),

(3,'NINA@EXAMPLE.TEST');

Запрос

SELECT id, lower(email) AS normalized_email

FROM clients

ORDER BY id;

Ожидаемый результат

[[1, "anna@example.test"], [2, "oleg@example.test"], [3, "nina@example.test"]]

Почему работает

lower приводит буквы к нижнему регистру.

Уже нижний email остаётся без изменений.

Нормализованный email удобнее использовать как ключ.

Где легко ошибиться

Нормализация регистра полезна для email, но не каждое текстовое поле можно бездумно переводить в lower.

Самостоятельная проверка

Почему lower помогает перед поиском дублей по email в клиентской базе?

Ответ: Так записи с разным регистром букв сравниваются как одно и то же значение.

Задача 3. Найти email с символом @

Рабочий вопрос

Нужно быстро отделить строки, которые хотя бы похожи на email, от явно неправильных значений.

Данные

CREATE TABLE contacts(id INTEGER, email TEXT);

INSERT INTO contacts VALUES

(1,'a@example.test'),

(2,'wrong-email'),

(3,'n@example.test'),

(4,'empty');

Запрос

SELECT id, email

FROM contacts

WHERE email LIKE '%@%'

ORDER BY id;

Ожидаемый результат

[[1, "a@example.test"], [3, "n@example.test"]]

Почему работает

LIKE '%@%' ищет символ @ в любой позиции строки.

Строки 1 и 3 содержат @.

Строки 2 и 4 не похожи на email по этому простому правилу.

Где легко ошибиться

Наличие @ ещё не доказывает валидность email. Это первичный фильтр, а не полноценная проверка адреса.

Самостоятельная проверка

Почему строка wrong-email не попала в результат этого простого фильтра?

Ответ: В ней нет символа @, а именно его ищет условие LIKE '%@%'.

Задача 4. Очистить телефон от пробелов и дефисов

Рабочий вопрос

Нужно привести телефоны к одному виду, чтобы одинаковые номера не расходились из-за форматирования.

Данные

CREATE TABLE phones(id INTEGER, phone TEXT);

INSERT INTO phones VALUES

(1,'+7 999 000 00 01'),

(2,'+7-999-000-00-02'),

(3,'+79990000003');

Запрос

SELECT id,

replace(replace(phone, ' ', ''), '-', '') AS clean_phone

FROM phones

ORDER BY id;

Ожидаемый результат

[[1, "+79990000001"], [2, "+79990000002"], [3, "+79990000003"]]

Почему работает

Внутренний replace убирает пробелы.

Внешний replace убирает дефисы.

Третий телефон уже был в чистом виде.

Где легко ошибиться

Такая чистка не проверяет длину и страну номера. После replace нужна отдельная проверка формата.

Самостоятельная проверка

Почему в запросе используются два вложенных replace, а не один вызов функции?

Ответ: Каждый replace заменяет только один тип символа: пробелы и дефисы удаляются отдельно.