SQL TRANSLATE: Per-Character Mapping, Deletion and Transliteration

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

Самый частый сценарий — подготовка значения для slug, кода или ключа: пробел и пунктуацию приводят к дефису, мусорные символы убирают. Поскольку каждый символ обрабатывается независимо, TRANSLATE нельзя обмануть порядком вхождений: набор '.,;' отработает одинаково в любой строке. Если такое преобразование повторяется в отчётах, проверках качества или миграциях, держите его в одном месте — view, generated column, CHECK или staging-шаге, — чтобы правило можно было протестировать и переиспользовать.

Базовый синтаксис

Сигнатура простая: TRANSLATE(text, from_set, to_set). Символ from_set[i] превращается в to_set[i].

SELECT TRANSLATE('abc', 'abc', 'xyz');  -- 'xyz'
SELECT TRANSLATE('a-b-c', 'abc', 'xyz'); -- 'x-y-z'

Дефисы остались на месте: их нет в from_set, значит они не трогаются. TRANSLATE обрабатывает каждый символ независимо и за один проход.

• Соответствие идёт строго по позиции: первый к первому, второй ко второму.
• Символы вне from_set копируются без изменений.
• Регистр важен: a и A — разные символы.

Удаление символов: когда to_set короче

Если во втором наборе для символа нет пары, этот символ удаляется. Это самый практичный приём TRANSLATE.

-- Normalize phone numbers: keep only digits-ish, drop separators
SELECT id, TRANSLATE(name, ' .,()-+', '') AS cleaned
FROM users;

Здесь to_set пустой, поэтому все перечисленные разделители просто исчезают. Частый случай — вычистить форматирование из телефонов или артикулов:

-- Strip spaces, dashes and parens from a stored code
SELECT TRANSLATE('+1 (555) 123-45-67', ' ()-+', '');
-- '15551234567'

То же самое для статусов заказов — заменить подчёркивания пробелом перед экспортом:

SELECT id, TRANSLATE(status, '_', ' ') AS label
FROM orders;

Транслитерация в один проход

TRANSLATE отлично строит slug-подобные значения или нормализует символы. Сопоставьте «опасные» символы безопасным за один вызов:

-- Map accented look-alikes and odd punctuation in one shot
SELECT id,
       TRANSLATE(LOWER(name), ' /\\.', '----') AS slug_part
FROM employees;

Здесь стоит читать литералы буквально. В from_set записано ' /\\.': внутри одинарных кавычек PostgreSQL \\ — это два символа обратной косой черты, поэтому набор содержит пять символов — пробел, прямой слэш, два бэкслеша и точку. В to_set '----' всего четыре дефиса. Пробел, прямой слэш и обратная косая черта получают дефис по позиции; второй бэкслеш — повтор и потому игнорируется (см. подсказку ниже), а точка остаётся без пары и удаляется. Если точку нужно сохранить как дефис, добавьте ей пятый дефис в to_set; длины наборов должны точно отражать ваше намерение, иначе непарные символы из from_set будут не заменены, а удалены.

Gotcha: символы в from_set должны быть уникальны. Если символ повторяется, действует только его первое вхождение. TRANSLATE('a', 'aa', 'xy') даёт 'x', а не 'y' — вторая a игнорируется.

Главное отличие от REPLACE

REPLACE ищет и заменяет подстроку целиком; TRANSLATE работает посимвольно и независимо от порядка.

-- REPLACE swaps a whole substring
SELECT REPLACE('a.b.c', '.', '_');     -- 'a_b_c'

-- TRANSLATE maps single chars; great for sets
SELECT TRANSLATE('a.b,c;d', '.,;', '___'); -- 'a_b_c_d'

Чтобы убрать три разных разделителя через REPLACE, понадобятся три вложенных вызова; TRANSLATE делает это одним аргументом-набором.

• Нужно заменить слово или многосимвольный токен — это REPLACE.
• Нужно отобразить или удалить набор отдельных символов — это TRANSLATE.

Особенности движков

• PostgreSQL: полноценный TRANSLATE(text, from, to), удаление при коротком to_set — всё как описано выше на примере с точкой.
• Oracle: тоже есть, но to_set не должен быть пустой строкой (она трактуется как NULL, и результат становится NULL). Чтобы удалять символы, подставляйте непустой to_set короче from_set.
• MySQL/MariaDB: функции TRANSLATE нет. Эмулируйте через вложенные REPLACE или REGEXP_REPLACE.
• ClickHouse: есть translate(s, from, to) и translateUTF8 для корректной работы с многобайтовыми символами.

-- ClickHouse: byte-wise vs UTF-8 aware
SELECT translateUTF8(name, 'aeiou', 'AEIOU') FROM users;

Главная ловушка TRANSLATE — не сама замена, а крайние случаи на стыке наборов: from_set длиннее to_set (часть символов удаляется), дубль в from_set (учитывается только первое вхождение) и многобайтовые символы при байтовом варианте функции. Перед переносом slug-логики между PostgreSQL, MySQL и ClickHouse прогоните маленькую таблицу с NULL, пустой строкой и не-ASCII: движки сходятся на чистых данных и расходятся ровно на этих краях, как точка в примере выше.

Если результат TRANSLATE идёт в ключ, публичный идентификатор или slug, зафиксируйте трактовку рядом с примером: какие символы заменяются, какие удаляются, что станет с дубликатами и не-ASCII. Проверьте пустую строку и строку без целевых символов отдельным тестом. На больших таблицах помните, что TRANSLATE над колонкой в WHERE закрывает путь к обычному индексу — под такой фильтр нужен функциональный индекс по тому же выражению.

Помните: в большинстве движков TRANSLATE работает по кодовым точкам или байтам, поэтому для не-ASCII выбирайте UTF-8-вариант (translateUTF8 в ClickHouse), если он есть.