TRIM, SUBSTRING and REPLACE: Cleaning Up Strings in SQL

В базах данных строки редко бывают идеально чистыми.

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

• лишний пробел в начале или в конце строки;
• двойные пробелы внутри имени;
• телефон с дефисами, скобками и пробелами;
• страна написана то как Vietnam, то как vietnam;
• статус заказа пришёл как Paid, paid, PAID или paid;
• из CSV-файла приехали невидимые символы;
• внешний сервис прислал код в неожиданном формате.

На первый взгляд это мелочи. Но именно из-за таких мелочей потом ломаются отчёты.

Например, вы хотите посчитать пользователей по странам, а SQL считает Vietnam и Vietnam разными значениями. Или пытаетесь соединить две таблицы по email, но часть строк не соединяется, потому что в одном месте email записан как user@mail.com, а в другом — как user@mail.com с пробелом в конце.

Для таких задач в SQL есть базовые строковые функции:

• TRIM — убирает лишние символы по краям строки;
• SUBSTRING — вырезает часть строки;
• REPLACE — заменяет одну подстроку на другую.

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

В примерах будем использовать несколько простых таблиц:

users(id, email, name, country, created_at)
orders(id, user_id, amount, status, created_at)
employees(id, name, manager_id, dept, salary)

Зачем вообще очищать строки

Допустим, в таблице users есть такие данные:

id | email              | name       | country
---+--------------------+------------+------------
1  | anna@mail.com      | Anna       | Vietnam
2  | bob@mail.com       | Bob        |  Vietnam
3  | kate@mail.com      | Kate       | Vietnam
4  | tom@mail.com       | Tom        |  vietnam

Для человека все эти строки про одну страну — Vietnam.

А для базы данных это могут быть разные значения:

'Vietnam'
' Vietnam'
'Vietnam '
' vietnam '

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

SELECT country, COUNT(*) AS users_count
FROM users
GROUP BY country;

Вместо одной строки по Vietnam можно получить несколько:

country   | users_count
----------+------------
Vietnam   | 1
 Vietnam  | 1
Vietnam   | 1
 vietnam  | 1

Визуально это выглядит почти одинаково, но для SQL это разные строки.

Чтобы такого не было, данные часто нормализуют прямо в запросе:

SELECT
  LOWER(TRIM(country)) AS clean_country,
  COUNT(*) AS users_count
FROM users
GROUP BY LOWER(TRIM(country));

Теперь все варианты превратятся в одно значение:

vietnam

И отчёт станет корректнее.

TRIM: убираем лишние пробелы по краям

TRIM — одна из самых полезных функций для очистки строк.

По умолчанию она убирает пробелы с начала и конца строки.

Пример:

SELECT TRIM('  Vietnam  ') AS clean_country;

Результат:

clean_country
-------------
Vietnam

Были пробелы слева и справа, а после TRIM остался чистый текст.

Важно: TRIM убирает пробелы именно по краям строки. Пробелы внутри строки она не трогает.

Например:

SELECT TRIM('  Anna   Smith  ') AS clean_name;

Результат:

clean_name
-----------
Anna   Smith

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

Это нормальное поведение. TRIM не пытается «умно» форматировать весь текст. Она просто очищает края строки.

TRIM на примере таблицы users

Допустим, в таблице users поле name заполнено неаккуратно:

id | name
---+------------
1  | Anna
2  |  Bob
3  | Kate
4  |  Tom

Чтобы вывести имена без лишних пробелов:

SELECT
  id,
  TRIM(name) AS clean_name
FROM users;

Результат:

id | clean_name
---+-----------
1  | Anna
2  | Bob
3  | Kate
4  | Tom

Это особенно полезно после импорта из CSV, Excel, Google Sheets или внешних CRM-систем, где пользователи часто случайно оставляют пробелы.

TRIM только слева или только справа

Иногда нужно убрать пробелы только с одной стороны.

Например, только слева:

SELECT TRIM(LEADING FROM '  Vietnam  ') AS result;

Результат:

Vietnam

Пробелы слева убрались, справа остались.

Только справа:

SELECT TRIM(TRAILING FROM '  Vietnam  ') AS result;

Результат:

  Vietnam

Пробелы справа убрались, слева остались.

С двух сторон:

SELECT TRIM(BOTH FROM '  Vietnam  ') AS result;

Результат:

Vietnam

Чаще всего в реальных запросах используют обычный TRIM, потому что обычно нужно убрать мусор и слева, и справа.

LTRIM и RTRIM

Во многих СУБД есть более короткие функции:

LTRIM(string)
RTRIM(string)

LTRIM убирает пробелы слева:

SELECT LTRIM('  Vietnam') AS result;

RTRIM убирает пробелы справа:

SELECT RTRIM('Vietnam  ') AS result;

В PostgreSQL также есть BTRIM, который похож на TRIM с двух сторон:

SELECT BTRIM('  Vietnam  ') AS result;

Для новичка достаточно запомнить главное:

• TRIM — убрать пробелы по краям;
• LTRIM — убрать слева;
• RTRIM — убрать справа.

TRIM может убирать не только пробелы

TRIM умеет убирать не только обычные пробелы, но и конкретные символы.

Например, есть строка:

000123

Хотим убрать нули слева:

SELECT TRIM(LEADING '0' FROM '000123') AS result;

Результат:

123

Можно убрать нули с двух сторон:

SELECT TRIM(BOTH '0' FROM '000123000') AS result;

Результат:

123

Ещё пример: убрать дефисы по краям строки.

SELECT TRIM(BOTH '-' FROM '---hello---') AS result;

Результат:

hello

Но важно понимать ограничение: TRIM работает только по краям.

Например:

SELECT TRIM(BOTH '-' FROM '---he-llo---') AS result;

Результат:

he-llo

Дефис внутри строки остался, потому что он не находится на краю.

Важное ограничение TRIM

TRIM хорошо убирает обычные пробелы по краям, но не всегда решает все проблемы с «невидимым мусором».

В строках могут быть:

• табы;
• переносы строк;
• неразрывные пробелы;
• двойные пробелы внутри текста;
• служебные символы из внешних систем.

Например, строка может выглядеть как обычное имя:

Anna

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

Для таких случаев часто используют REPLACE или REGEXP_REPLACE, о которых поговорим ниже.

SUBSTRING: вырезаем часть строки

SUBSTRING нужна, когда из строки нужно достать только определённый кусок.

Например:

• первые 3 символа кода;
• домен из email;
• год из строки с датой;
• часть артикула;
• короткий префикс отдела;
• последние цифры номера.

Простейший пример:

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS part;

Результат:

part
----
Post

Здесь мы говорим:

возьми строку 'PostgreSQL'
начни с позиции 1
возьми 4 символа

Важно: в SQL позиции в строке обычно считаются с 1, а не с 0.

То есть:

P o s t g r e S Q L
1 2 3 4 5 6 7 8 9 10

Поэтому FROM 1 FOR 4 возвращает первые четыре символа.

Две формы записи SUBSTRING

В PostgreSQL можно встретить такую форму:

SUBSTRING(string FROM start FOR length)

Например:

SELECT SUBSTRING(country FROM 1 FOR 3) AS country_prefix
FROM users;

И такую форму:

SUBSTRING(string, start, length)

Например:

SELECT SUBSTRING(country, 1, 3) AS country_prefix
FROM users;

Обе записи в PostgreSQL делают одно и то же.

Если country = 'Vietnam', результат будет:

Vie

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

SUBSTRING на примере кода сотрудника

Допустим, есть таблица employees:

id | name          | dept
---+---------------+------------
1  | Anna Smith    | Marketing
2  | Bob Johnson   | Analytics
3  | Kate Brown    | Development

Мы хотим сделать короткий код сотрудника:

первые 3 буквы отдела + первые 2 буквы имени

Например:

MAR-AN
ANA-BO
DEV-KA

Запрос:

SELECT
  id,
  UPPER(SUBSTRING(TRIM(dept) FROM 1 FOR 3)) || '-' ||
  UPPER(SUBSTRING(TRIM(name) FROM 1 FOR 2)) AS emp_code
FROM employees;

Что здесь происходит:

1. TRIM(dept) убирает лишние пробелы у отдела.
2. SUBSTRING(... FROM 1 FOR 3) берёт первые 3 символа отдела.
3. TRIM(name) убирает лишние пробелы у имени.
4. SUBSTRING(... FROM 1 FOR 2) берёт первые 2 символа имени.
5. UPPER переводит результат в верхний регистр.
6. || '-' || склеивает части через дефис.

Результат:

id | emp_code
---+----------
1  | MAR-AN
2  | ANA-BO
3  | DEV-KA

Такой пример хорошо показывает, что строковые функции часто используют не по одной, а цепочкой.

Как достать домен из email

Одна из частых задач — получить домен из email.

Например, из строки:

anna@gmail.com

нужно получить:

gmail.com

Для этого нужно найти позицию символа @, а затем взять всё, что идёт после него.

В PostgreSQL позицию символа можно найти через POSITION:

SELECT POSITION('@' IN 'anna@gmail.com') AS at_position;

Результат:

at_position
-----------
5

Символ @ стоит на пятой позиции.

Теперь можно взять подстроку после него:

SELECT SUBSTRING('anna@gmail.com' FROM POSITION('@' IN 'anna@gmail.com') + 1) AS domain;

Результат:

domain
---------
gmail.com

То же самое для таблицы users:

SELECT
  id,
  email,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS email_domain
FROM users;

Результат:

id | email           | email_domain
---+-----------------+-------------
1  | anna@gmail.com  | gmail.com
2  | bob@mail.ru     | mail.ru
3  | kate@yahoo.com  | yahoo.com

Здесь SUBSTRING используется не с фиксированной позицией, а с позицией, которую мы сначала вычислили.

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

REPLACE: заменяем одну часть строки на другую

REPLACE нужна, когда в строке нужно заменить одну подстроку на другую.

Синтаксис обычно такой:

REPLACE(source, from_text, to_text)

То есть:

в строке source замени from_text на to_text

Пример:

SELECT REPLACE('Hello, SQL!', 'SQL', 'PostgreSQL') AS result;

Результат:

Hello, PostgreSQL!

Можно заменить символ на пустую строку. Так часто удаляют лишние символы.

Например, убрать дефисы из телефона:

SELECT REPLACE('+7-999-123-45-67', '-', '') AS phone_clean;

Результат:

+79991234567

REPLACE заменяет все найденные вхождения, а не только первое.

REPLACE на примере статусов

Допустим, в таблице orders статусы записаны так:

id | status
---+----------------
1  | paid
2  | waiting payment
3  | payment failed

Для технического ключа или URL иногда хочется заменить пробелы на подчёркивания:

SELECT
  id,
  REPLACE(status, ' ', '_') AS status_code
FROM orders;

Результат:

id | status_code
---+----------------
1  | paid
2  | waiting_payment
3  | payment_failed

Так можно быстро привести строку к более удобному формату.

Несколько REPLACE подряд

Иногда нужно убрать сразу несколько символов.

Например, есть телефон:

+7 (999) 123-45-67

Нужно убрать пробелы, скобки и дефисы.

Можно вложить несколько REPLACE друг в друга:

SELECT
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE('+7 (999) 123-45-67', ' ', ''),
      '(', ''),
    ')', ''),
  '-', '') AS phone_clean;

Результат:

+79991234567

Выглядит немного громоздко, но идея простая:

1. убрали пробелы;
2. убрали открывающую скобку;
3. убрали закрывающую скобку;
4. убрали дефисы.

Для простых задач этого достаточно.

Но если нужно удалить всё, кроме цифр, лучше использовать регулярные выражения.

REPLACE не понимает регулярные выражения

Важный момент: REPLACE ищет обычный текст, а не шаблон.

Например:

SELECT REPLACE('abc123', '[0-9]', '') AS result;

Кто-то может ожидать, что этот запрос удалит цифры. Но этого не произойдёт.

Почему?

Потому что REPLACE будет искать буквальную строку:

[0-9]

А не «любую цифру».

Если нужно работать с шаблонами, в PostgreSQL используют REGEXP_REPLACE.

Например, оставить только цифры:

SELECT REGEXP_REPLACE('+7 (999) 123-45-67', '[^0-9]', '', 'g') AS digits_only;

Результат:

79991234567

Что здесь происходит:

• [^0-9] означает «любой символ, который не является цифрой»;
• '' означает «заменить на пустоту»;
• 'g' означает «заменить все такие символы, а не только первый».

Для новичка можно запомнить так:

REPLACE — простая замена конкретного текста. REGEXP_REPLACE — замена по шаблону.

Собираем нормализованный ключ

В реальных задачах строковые функции часто комбинируются.

Допустим, нам нужно сделать нормализованный ключ из страны пользователя и статуса заказа.

Есть данные:

country     | status
------------+----------------
 Vietnam    | waiting payment
vietnam     | Waiting Payment
VIETNAM     | waiting payment

Для человека это один и тот же смысл. Но для базы это разные строки.

Соберём единый ключ:

SELECT
  o.id,
  LOWER(TRIM(u.country)) || '_' ||
  REPLACE(LOWER(TRIM(o.status)), ' ', '_') AS norm_key
FROM orders o
JOIN users u ON u.id = o.user_id;

Результат может быть таким:

id | norm_key
---+-------------------------
1  | vietnam_waiting_payment
2  | vietnam_waiting_payment
3  | vietnam_waiting_payment

Разберём по частям.

TRIM(u.country)

убирает пробелы по краям страны.

LOWER(TRIM(u.country))

переводит страну в нижний регистр.

TRIM(o.status)

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

LOWER(TRIM(o.status))

переводит статус в нижний регистр.

REPLACE(LOWER(TRIM(o.status)), ' ', '_')

заменяет пробелы внутри статуса на подчёркивания.

А оператор || склеивает строки.

Такой подход полезен для:

• дедупликации;
• построения технических ключей;
• подготовки данных для отчётов;
• сравнения строк из разных источников;
• соединения таблиц по текстовым полям.

Очистка строк перед JOIN

Одна из неприятных проблем — когда JOIN не находит совпадения из-за пробелов или регистра.

Допустим, в одной таблице email хранится так:

anna@mail.com

А в другой так:

 Anna@Mail.com

Для человека это один и тот же email. Для SQL — разные строки.

Можно нормализовать значения прямо в условии соединения:

SELECT
  u.id,
  u.email,
  o.id AS order_id
FROM users u
JOIN orders o
  ON LOWER(TRIM(u.email)) = LOWER(TRIM(o.email));

Но здесь есть важный нюанс.

Такой подход может быть полезен для разовой проверки или очистки данных. Но если это постоянная логика в большом проекте, лучше хранить email уже в нормализованном виде или завести отдельную колонку с очищенным значением.

Почему?

Потому что функции в условии JOIN могут мешать эффективному использованию индексов. На больших таблицах это может замедлить запрос.

Хороший рабочий подход:

1. на этапе загрузки очищать email;
2. хранить его в едином формате;
3. уже потом спокойно делать обычный JOIN.

Например:

ON u.email = o.email

Чем чище данные в таблицах, тем проще и быстрее запросы.

Очистка строк перед GROUP BY

Ещё один частый сценарий — группировка.

Плохие или неодинаковые строки дают лишние группы.

Например:

SELECT country, COUNT(*) AS users_count
FROM users
GROUP BY country;

Если в country есть пробелы и разный регистр, результат может быть грязным.

Лучше так:

SELECT
  LOWER(TRIM(country)) AS clean_country,
  COUNT(*) AS users_count
FROM users
GROUP BY LOWER(TRIM(country))
ORDER BY users_count DESC;

Теперь значения:

Vietnam
 vietnam
VIETNAM
Vietnam

попадут в одну группу:

vietnam

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

SUBSTRING и нумерация с единицы

Новички часто ошибаются из-за привычки из языков программирования.

Во многих языках строки нумеруются с нуля:

0 1 2 3
a b c d

Но в SQL чаще всего позиции начинаются с единицы:

1 2 3 4
a b c d

Поэтому, чтобы взять первые три символа строки, нужно писать:

SELECT SUBSTRING('abcdef' FROM 1 FOR 3) AS part;

Результат:

abc

Не нужно писать FROM 0.

В PostgreSQL выражение вроде:

SELECT SUBSTRING('abcdef' FROM 0 FOR 3) AS part;

может дать не тот результат, который ожидает разработчик после JavaScript, Python или Java.

Простое правило:

В SQL начинайте вырезать строку с позиции 1.

Практические шаблоны

Убрать пробелы по краям

SELECT TRIM(name) AS clean_name
FROM users;

Привести страну к единому виду

SELECT LOWER(TRIM(country)) AS clean_country
FROM users;

Взять первые 3 символа строки

SELECT SUBSTRING(country FROM 1 FOR 3) AS prefix
FROM users;

Достать домен из email

SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

Заменить пробелы на подчёркивания

SELECT REPLACE(status, ' ', '_') AS status_code
FROM orders;

Убрать дефисы из строки

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

Оставить только цифры в PostgreSQL

SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only
FROM users;

Сделать нормализованный ключ

SELECT
  LOWER(TRIM(country)) || '_' || REPLACE(LOWER(TRIM(status)), ' ', '_') AS norm_key
FROM users;

Отличия PostgreSQL, MySQL и ClickHouse

Идея строковых функций в разных СУБД похожа, но синтаксис может отличаться.

PostgreSQL

В PostgreSQL можно использовать обе формы SUBSTRING:

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);

и:

SELECT SUBSTRING('PostgreSQL', 1, 4);

Также есть короткий вариант SUBSTR:

SELECT SUBSTR('PostgreSQL', 1, 4);

Для замены используется REPLACE:

SELECT REPLACE('a-b-c', '-', '');

Для регулярных выражений — REGEXP_REPLACE:

SELECT REGEXP_REPLACE('+7 (999) 123-45-67', '[^0-9]', '', 'g');

MySQL

В MySQL SUBSTRING и SUBSTR тоже используются для вырезания части строки:

SELECT SUBSTRING('PostgreSQL', 1, 4);

или:

SELECT SUBSTR('PostgreSQL', 1, 4);

Также в MySQL можно использовать форму:

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);

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

Например:

SELECT SUBSTRING('PostgreSQL', -3);

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

ClickHouse

В ClickHouse чаще используется функция substring в позиционной форме:

SELECT substring('PostgreSQL', 1, 4);

Для замены всех вхождений обычно используют replaceAll:

SELECT replaceAll('a-b-c', '-', '');

Для обрезки пробелов есть функции:

trimBoth(string)
trimLeft(string)
trimRight(string)

Например:

SELECT trimBoth('  Vietnam  ');

Общая идея везде одинаковая:

• обрезать лишнее;
• вырезать нужную часть;
• заменить ненужные символы;
• привести строки к единому виду.

Меняются только названия и детали синтаксиса.

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

TRIM, SUBSTRING и REPLACE — базовые функции для работы со строками в SQL.

TRIM убирает лишние символы по краям строки:

SELECT TRIM('  Vietnam  ');

SUBSTRING вырезает часть строки по позиции:

SELECT SUBSTRING('Vietnam' FROM 1 FOR 3);

REPLACE заменяет одну подстроку на другую:

SELECT REPLACE('waiting payment', ' ', '_');

Эти функции особенно полезны, когда данные пришли из внешних источников:

• CSV;
• Excel;
• CRM;
• API;
• пользовательских форм;
• старых баз данных.

Но важно помнить ограничения:

• TRIM не чистит середину строки;
• SUBSTRING считает позиции с 1;
• REPLACE не работает с регулярными выражениями;
• для сложной очистки в PostgreSQL нужен REGEXP_REPLACE;
• функции в JOIN и WHERE могут ухудшать использование индексов на больших таблицах.

Короткий вывод

Строковые функции — это не просто косметика. Они напрямую влияют на качество отчётов, корректность JOIN, количество дублей и доверие к данным.

Если в таблице есть значения вроде:

'Vietnam'
' Vietnam'
'vietnam '
'VIETNAM'

то для человека это одно и то же, а для базы — разные строки.

Поэтому хороший SQL-запрос часто сначала приводит текст к нормальному виду:

LOWER(TRIM(country))

А уже потом фильтрует, группирует или соединяет данные.

Можно запомнить простую логику:

TRIM — убрать лишнее по краям. SUBSTRING — достать нужный кусок. REPLACE — заменить ненужные символы.

Эти три функции закрывают огромную часть повседневных задач по очистке строк в SQL.