BTRIM, LTRIM and RTRIM in SQL: Trimming Spaces and Any Characters

Грязные строки — это норма: лишние пробелы из форм, ведущие нули в кодах, хвостовые слеши в URL. BTRIM, LTRIM и RTRIM обрезают символы по краям строки и решают большую часть этих задач одной функцией.

Ключевое свойство всех трех функций: они срезают символы только с краев и никогда не трогают середину. BTRIM('a b', ' ') оставит 'a b' без изменений — внутренние пробелы между словами сохранятся, уйдут лишь ведущие и хвостовые. Это отличает их от REPLACE, которое вырезало бы пробелы по всей строке. Поэтому *TRIM безопасны для нормализации перед уникальным индексом или сравнением: лишний пробел или слеш на границе превращает одинаковые значения в разные, и именно его убирает обрезка краев, не повреждая значимое содержимое. По умолчанию все три режут пробелы, но второй аргумент позволяет задать любой набор символов — нули, слеши, дефисы, символы валюты — и в этом их главное преимущество перед стандартным TRIM.

Три функции и что они режут

В PostgreSQL семейство устроено симметрично:

• LTRIM(s) — убирает символы слева (в начале);
• RTRIM(s) — убирает символы справа (в конце);
• BTRIM(s) — убирает с обоих концов (both).

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

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

Важно понимать: второй аргумент — это не подстрока, а множество символов. BTRIM('xxyhelloyxx', 'xy') уберёт все ведущие и хвостовые символы, входящие в {'x','y'}, в любом порядке, пока не встретит что-то другое.

Нормализация пользовательских данных

Самый частый сценарий — чистка email и name перед вставкой или сравнением. Пробелы по краям ломают уникальность и join-ы.

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

-- Fix existing rows in place
UPDATE users
SET name = BTRIM(name)
WHERE name <> BTRIM(name);

Условие name <> BTRIM(name) обновляет только реально грязные строки — это дешевле и не засоряет журнал лишними записями.

Срезаем произвольные символы: нули и слеши

Здесь набор символов раскрывается полностью. Уберём ведущие нули из кода и хвостовой слеш из пути.

SELECT
  LTRIM('00042', '0')                 AS code,    -- '42'
  RTRIM('https://shop.dev/api/', '/') AS endpoint,-- 'https://shop.dev/api'
  BTRIM('--draft--', '-')             AS status;  -- 'draft'

Практичный пример над orders: статусы иногда приходят с маркерами-обёртками, а суммы — как текст с символом валюты.

SELECT
  id,
  BTRIM(status, '*')                     AS status,
  LTRIM(CAST(amount AS text), '$') AS amount_text
FROM orders
WHERE status LIKE '%*%';

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

BTRIM/LTRIM/RTRIM — это диалектные имена PostgreSQL. Стандарт SQL описывает один оператор TRIM с ключевыми словами направления:

SELECT
  TRIM(LEADING  '0' FROM '00042')  AS a,   -- same as LTRIM('00042','0')
  TRIM(TRAILING '/' FROM 'path/')  AS b,   -- same as RTRIM('path/','/')
  TRIM(BOTH     ' ' FROM '  x  ')  AS c;   -- same as BTRIM('  x  ')

Соответствие однозначное:

• TRIM(LEADING c FROM s) = LTRIM(s, c);
• TRIM(TRAILING c FROM s) = RTRIM(s, c);
• TRIM(BOTH c FROM s) = BTRIM(s, c).

Есть тонкость: в стандартном TRIM символ для обрезки — это одиночный символ, а в BTRIM/LTRIM второй аргумент — это набор символов. Для «срезать любой из этих символов» удобнее именно *TRIM.

Различия в MySQL и ClickHouse

• MySQL: TRIM, LTRIM, RTRIM есть, но у LTRIM/RTRIM нет второго аргумента — они режут только пробелы. Функции BTRIM нет; используйте TRIM(BOTH 'x' FROM s), но помните, что аргумент здесь — подстрока (remstr), а не множество символов.
• ClickHouse: есть trimLeft, trimRight, trimBoth — но они тоже обрезают только пробелы. Для произвольных символов берите trim(LEADING 'x' FROM s) или регулярные выражения вроде replaceRegexpOne.

Еще одно различие касается NULL и пустой строки: BTRIM(NULL) возвращает NULL, а BTRIM(' ') (только пробелы) даст пустую строку '', а не NULL. Если после очистки нужно отбраковать «пустые» значения, проверяйте именно BTRIM(col) <> '', а не col IS NOT NULL — строка из одних пробелов пройдет вторую проверку, но не первую. При переносе того же выражения между PostgreSQL, MySQL и ClickHouse прогоните BTRIM/TRIM на наборе с NULL, пустой строкой, строкой из одних пробелов и Unicode: движки совпадают на чистых данных и расходятся именно на этих краях.

Помните и про индексы: BTRIM(email) в WHERE — это функция над колонкой, и обычный B-tree индекс по email для нее не сработает. Если очистка краев нужна на горячем пути фильтрации, храните уже нормализованное значение в generated column или заводите функциональный индекс CREATE INDEX ON users (BTRIM(email)). Тогда *TRIM останется в запросе, но не превратит поиск в полное сканирование.

Главная ловушка — перепутать «набор символов» и «подстроку». RTRIM('abcxyz', 'zyx') в PostgreSQL вернёт 'abc' (срезаны все символы из набора {z,y,x}), а вот TRIM(TRAILING 'zyx' FROM 'abcxyz') в MySQL вернёт 'abc' лишь потому, что 'xyz' совпало целиком как подстрока. Поменяйте порядок букв — и поведение разойдётся. Всегда проверяйте, чем оперирует ваша СУБД: множеством символов или подстрокой.