BTRIM, LTRIM и RTRIM в PostgreSQL: как аккуратно чистить строки по краям

Грязные строки в базе — это не редкость, а обычная жизнь. Пользователь случайно поставил пробел после email. Интеграция прислала код товара с ведущими нулями. В URL то есть слеш в конце, то нет. Статус заказа пришёл не как paid, а как *paid*.

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

Строка admin@example.com и строка admin@example.com с пробелом в конце — это два разных значения. URL https://shop.dev/api и https://shop.dev/api/ тоже разные. Из-за таких мелочей ломаются сравнения, не срабатывают JOIN, появляются дубли и странные ошибки в уникальных индексах.

В PostgreSQL для такой уборки есть семейство функций: BTRIM, LTRIM и RTRIM. Они обрезают лишние символы по краям строки: слева, справа или с обеих сторон.

Главное правило: эти функции трогают только края строки и не лезут в середину. Именно поэтому они удобны для безопасной нормализации данных.

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

Представьте таблицу пользователей:

SELECT id, email
FROM users;

Внутри могут лежать такие значения:

id | email
---+---------------------
1  | alex@example.com
2  | alex@example.com 
3  |   maria@example.com

Для человека это почти одно и то же: email как email. Но для SQL это разные строки, потому что пробелы — тоже символы.

Из-за этого запрос может вести себя неожиданно:

SELECT id
FROM users
WHERE email = 'alex@example.com';

Он найдёт строку без пробела, но может не найти строку, где пробел спрятался в конце.

Здесь и помогает обрезка:

SELECT id, BTRIM(email) AS email_clean
FROM users;

BTRIM(email) уберёт пробелы слева и справа. Содержимое внутри строки при этом останется как есть.

Три функции: слева, справа и с обеих сторон

В PostgreSQL есть три похожие функции:

• LTRIM убирает символы слева, то есть в начале строки;
• RTRIM убирает символы справа, то есть в конце строки;
• BTRIM убирает символы с обеих сторон.

Название BTRIM можно запомнить как both trim: обрезать с двух сторон.

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

SELECT
    LTRIM('   hello   ') AS left_only,
    RTRIM('   hello   ') AS right_only,
    BTRIM('   hello   ') AS both_sides;

Результат будет таким:

left_only | right_only | both_sides
----------+------------+-----------
hello     |    hello   | hello

Разница простая:

LTRIM убрал пробелы только в начале.

RTRIM убрал пробелы только в конце.

BTRIM убрал пробелы и в начале, и в конце.

TRIM не трогает середину строки

Это очень важное свойство.

SELECT BTRIM('a  b') AS result;

Результат:

result
------
a  b

Два пробела между a и b остались на месте. Почему? Потому что они находятся внутри строки, а не по краям.

Функции семейства TRIM работают как аккуратные ножницы: подрезают только торчащие края, но не режут середину.

Если вам нужно убрать пробелы вообще везде, это уже другая задача. Например, через REPLACE:

SELECT REPLACE('a  b', ' ', '') AS result;

Результат:

result
------
ab

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

Поэтому важно не путать задачи:

BTRIM, LTRIM, RTRIM — для очистки краёв.

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

Второй аргумент: что именно обрезать

По умолчанию функции убирают пробелы. Но можно передать второй аргумент — набор символов, которые нужно срезать с края.

Например, уберём ведущие нули:

SELECT LTRIM('00042', '0') AS code;

Результат:

code
----
42

Уберём слеш справа:

SELECT RTRIM('https://shop.dev/api/', '/') AS endpoint;

Результат:

endpoint
--------------------
https://shop.dev/api

Уберём дефисы с двух сторон:

SELECT BTRIM('--draft--', '-') AS status;

Результат:

status
------
draft

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

Второй аргумент — это набор символов, а не слово

Здесь есть ловушка, которую важно понять сразу.

В PostgreSQL второй аргумент у BTRIM, LTRIM и RTRIM — это не цельная подстрока, а набор отдельных символов.

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

SELECT BTRIM('xxyhelloyxx', 'xy') AS result;

Результат:

result
------
hello

PostgreSQL воспринимает 'xy' как набор символов: можно убирать x и можно убирать y.

Он идёт с краёв строки и срезает всё, что входит в этот набор:

x x y hello y x x

Слева убрались x, x, y.

Справа убрались y, x, x.

В середине осталось hello.

Порядок символов во втором аргументе не означает, что PostgreSQL ищет именно такую подстроку. Запись 'xy' означает: «убирай символ x и символ y, пока они стоят с края».

Практический пример: чистим email

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

Обычно у email нужно убрать пробелы по краям и привести буквы к нижнему регистру:

SELECT
    id,
    BTRIM(LOWER(email)) AS email_clean
FROM users;

Если в таблице лежит значение с пробелами и разным регистром:

  Alex@Example.com 

после очистки получится:

alex@example.com

Такое значение удобнее сравнивать, искать и использовать в уникальности.

Например, можно найти пользователей, у которых после очистки email не пустой:

SELECT id, BTRIM(LOWER(email)) AS email_clean
FROM users
WHERE BTRIM(email) <> '';

Почему проверяем именно BTRIM(email) <> ''?

Потому что строка из одних пробелов не равна NULL. Например:

'   '

Это строка. Просто внутри неё только пробелы. Проверка email IS NOT NULL такую строку пропустит. А вот BTRIM(email) <> '' после очистки поймёт, что полезного значения там нет.

Обновляем грязные строки в таблице

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

Например, убрать лишние пробелы у имён:

UPDATE users
SET name = BTRIM(name)
WHERE name <> BTRIM(name);

Здесь условие важно.

Без WHERE PostgreSQL обновил бы все строки, даже те, где имя уже чистое. Это лишняя работа: больше записей в журнале, больше нагрузка, больше шансов задеть то, что трогать не нужно.

Условие:

WHERE name <> BTRIM(name)

означает: обновляй только те строки, где текущее значение отличается от очищенного.

Для базы это аккуратнее, а для вас — безопаснее.

Чистим URL от хвостового слеша

Ещё один частый пример — URL.

Для человека эти адреса могут казаться одинаковыми:

https://shop.dev/api
https://shop.dev/api/

Но для SQL это разные строки.

Если вы хотите хранить URL без слеша в конце, можно использовать RTRIM:

SELECT RTRIM(url, '/') AS url_clean
FROM endpoints;

Или исправить данные в таблице:

UPDATE endpoints
SET url = RTRIM(url, '/')
WHERE url <> RTRIM(url, '/');

Но будьте внимательны: RTRIM(url, '/') уберёт все слеши справа, а не только один.

Например:

SELECT RTRIM('https://shop.dev/api///', '/') AS url_clean;

Результат:

url_clean
--------------------
https://shop.dev/api

Это обычно удобно, но лучше понимать поведение заранее.

Убираем ведущие нули

Ведущие нули часто встречаются в кодах, номерах и идентификаторах из внешних систем.

SELECT LTRIM('00000042', '0') AS code;

Результат:

code
----
42

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

Не все ведущие нули являются мусором. Например, код товара 00042 может быть официальным артикулом, где нули важны. А вот текстовое число 00042, которое потом нужно сравнивать как число, можно очистить.

То есть LTRIM технически умеет убрать нули, но решение «можно ли их убирать» зависит от предметной области.

Чистим статусы и маркеры

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

Например:

*paid*
--draft--
[active]

Если нужно убрать звёздочки:

SELECT BTRIM('*paid*', '*') AS status;

Результат:

status
------
paid

Если нужно убрать дефисы:

SELECT BTRIM('--draft--', '-') AS status;

Результат:

status
------
draft

Можно применить это к таблице заказов:

SELECT
    id,
    BTRIM(status, '*') AS status_clean
FROM orders
WHERE status LIKE '%*%';

Так вы увидите очищенные статусы только у строк, где есть звёздочки.

Можно обрезать сразу несколько разных символов

Второй аргумент может содержать несколько символов.

Например, уберём пробелы, дефисы и звёздочки по краям:

SELECT BTRIM(' --*paid*-- ', ' -*') AS status;

Результат:

status
------
paid

PostgreSQL будет срезать с обеих сторон любые символы из набора:

space
-
*

Как только встретится символ, которого нет в наборе, обрезка остановится.

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

*paid*
--paid--
 paid 
-*paid*-

Один BTRIM может привести такие значения к одному виду.

NULL и пустая строка

BTRIM аккуратно работает с NULL.

Если передать NULL, результат тоже будет NULL:

SELECT BTRIM(NULL) AS result;

Результат:

result
------
NULL

А если передать строку из одних пробелов, результатом будет пустая строка:

SELECT BTRIM('   ') AS result;

Результат:

result
------

То есть это не NULL, а именно пустая строка ''.

Разница важная:

SELECT id
FROM users
WHERE BTRIM(name) <> '';

Такой запрос отфильтрует строки, где после очистки осталось хоть что-то полезное.

Если же написать только:

SELECT id
FROM users
WHERE name IS NOT NULL;

то строка из пробелов пройдёт проверку, потому что она действительно не NULL.

Связь со стандартным TRIM

В SQL есть стандартная конструкция TRIM. Она умеет делать примерно то же самое, но записывается иначе.

SELECT
    TRIM(LEADING '0' FROM '00042') AS a,
    TRIM(TRAILING '/' FROM 'path/') AS b,
    TRIM(BOTH ' ' FROM '  x  ') AS c;

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

TRIM(LEADING ... FROM s)  -> LTRIM
TRIM(TRAILING ... FROM s) -> RTRIM
TRIM(BOTH ... FROM s)     -> BTRIM

То есть:

SELECT LTRIM('00042', '0') AS result;

и

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

дают один и тот же результат:

result
------
42

В PostgreSQL многие разработчики любят BTRIM, LTRIM и RTRIM за короткую и понятную запись. А TRIM удобен, когда нужен более стандартный SQL-синтаксис.

Чем BTRIM отличается от REPLACE

Иногда новичок смотрит на BTRIM и думает: «Это же почти как заменить лишние символы на пустоту». Не совсем.

BTRIM убирает символы только по краям:

SELECT BTRIM('---a-b-c---', '-') AS result;

Результат:

result
------
a-b-c

Дефисы внутри строки остались.

А REPLACE заменяет символы везде:

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

Результат:

result
------
abc

Это уже совсем другое поведение.

Поэтому для нормализации краёв используйте BTRIM, LTRIM, RTRIM. Для полной замены внутри строки — REPLACE.

Индексы и производительность

Есть ещё одна важная практическая деталь.

Если у вас есть обычный индекс по email, запрос такого вида может использовать его:

SELECT id
FROM users
WHERE email = 'alex@example.com';

Но если вы применяете функцию к колонке:

SELECT id
FROM users
WHERE BTRIM(email) = 'alex@example.com';

обычный индекс по email уже может не помочь. Причина простая: в индексе лежит исходное значение email, а в запросе вы ищете по результату функции BTRIM(email).

Если такая очистка нужна редко — ничего страшного.

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

Первый — хранить уже очищенное значение отдельно. Например, в отдельной колонке.

Второй — создать функциональный индекс:

CREATE INDEX users_email_btrim_idx
ON users (BTRIM(email));

После этого PostgreSQL сможет быстрее искать по выражению BTRIM(email).

Ещё более практичный вариант — нормализовать данные на входе: очищать email перед вставкой и хранить в таблице уже аккуратное значение. Тогда в большинстве запросов не придётся постоянно вызывать функцию.

MySQL: похожие функции, но есть отличия

В MySQL тоже есть TRIM, LTRIM и RTRIM.

Но есть важная разница: LTRIM и RTRIM в MySQL обычно используются для удаления пробелов и не принимают второй аргумент так же, как PostgreSQL.

Например, в PostgreSQL можно написать:

SELECT LTRIM('00042', '0') AS code;

В MySQL такой перенос один в один может не сработать.

Для произвольных символов в MySQL чаще используют TRIM:

SELECT TRIM(LEADING '0' FROM '00042') AS code;

Или для удаления символов с обеих сторон:

SELECT TRIM(BOTH '-' FROM '--draft--') AS status;

При переносе запросов между PostgreSQL и MySQL обязательно проверяйте синтаксис. Названия похожи, но детали отличаются.

ClickHouse: свои имена и свои правила

В ClickHouse есть функции для обрезки пробельных символов, например trimLeft, trimRight и trimBoth.

Идея похожая:

SELECT trimBoth('   hello   ') AS result;

Результат:

result
------
hello

Но для произвольных символов поведение и набор функций отличаются от PostgreSQL. Там часто используют синтаксис trim или регулярные выражения.

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

SELECT replaceRegexpOne('---draft---', '^-+|-+$', '') AS status;

Поэтому при переносе логики очистки из PostgreSQL в ClickHouse лучше не полагаться на память, а отдельно проверить несколько крайних случаев: NULL, пустую строку, строку из пробелов, строку с Unicode-символами и строку с несколькими разными символами по краям.

Главная ловушка: набор символов и подстрока

Самая неприятная ошибка — думать, что второй аргумент в PostgreSQL означает цельную подстроку.

Например:

SELECT RTRIM('abcxyz', 'zyx') AS result;

Результат в PostgreSQL:

result
------
abc

Почему?

Потому что 'zyx' здесь означает набор символов: z, y, x.

PostgreSQL смотрит на конец строки и срезает все символы, которые входят в этот набор:

abc x y z

С конца стоят z, y, x. Все они входят в набор, поэтому остаётся abc.

Порядок во втором аргументе не обязан совпадать с порядком символов в строке. Это не поиск слова zyx. Это список разрешённых для удаления символов.

Запомните простую фразу: в PostgreSQL второй аргумент BTRIM, LTRIM и RTRIM — это набор символов для обрезки с края.

Когда использовать BTRIM, LTRIM и RTRIM

Используйте BTRIM, когда нужно убрать мусор с обеих сторон строки:

SELECT BTRIM(email) AS email_clean
FROM users;

Используйте LTRIM, когда мусор бывает только слева:

SELECT LTRIM(code, '0') AS code_clean
FROM products;

Используйте RTRIM, когда мусор бывает только справа:

SELECT RTRIM(url, '/') AS url_clean
FROM endpoints;

Если нужно привести значение к нижнему регистру и убрать пробелы, функции можно комбинировать:

SELECT BTRIM(LOWER(email)) AS email_clean
FROM users;

SQL хорошо работает, когда данные аккуратные. А BTRIM, LTRIM и RTRIM — простые инструменты, которые помогают сделать строки предсказуемыми.

Главное из статьи

BTRIM, LTRIM и RTRIM в PostgreSQL обрезают символы по краям строки.

LTRIM работает слева, RTRIM — справа, BTRIM — с обеих сторон.

По умолчанию они убирают пробелы:

SELECT BTRIM('   hello   ') AS result;

Но можно передать второй аргумент и указать, какие символы нужно срезать:

SELECT BTRIM('--draft--', '-') AS result;

Главное помнить: второй аргумент — это набор символов, а не цельная подстрока.

BTRIM('xxyhelloyxx', 'xy') убирает с краёв любые x и y, пока не встретит другой символ.

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

Для частых запросов вида WHERE BTRIM(email) = ... подумайте о функциональном индексе или храните уже очищенное значение. И всегда проверяйте отличия при переносе логики в MySQL или ClickHouse: названия похожи, но правила обрезки и синтаксис могут отличаться.