TRANSLATE в SQL: как заменять и удалять отдельные символы в строке

TRANSLATE — это функция для посимвольной замены.

Она берёт строку, набор символов «что ищем» и набор символов «на что меняем». Затем проходит по строке и заменяет каждый найденный символ на символ с такой же позиции во втором наборе.

Звучит немного сухо, поэтому представим простую таблицу соответствий:

Было	Стало
a	x
b	y
c	z

Тогда строка abc превратится в xyz.

SELECT TRANSLATE('abc', 'abc', 'xyz') AS result;

Результат:

result
xyz

Главная идея: TRANSLATE работает не со словами и не с подстроками, а с отдельными символами.

Именно этим он отличается от REPLACE.

REPLACE говорит: «Найди вот такой кусок текста и замени его целиком».
TRANSLATE говорит: «Каждый такой символ замени на соответствующий символ из другого набора».

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

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

В PostgreSQL синтаксис такой:

TRANSLATE(source_text, from_set, to_set)

Где:

• source_text — исходная строка;
• from_set — набор символов, которые нужно найти;
• to_set — набор символов, на которые нужно заменить.

Символы сопоставляются по позиции.

Первый символ из from_set заменяется на первый символ из to_set.
Второй — на второй.
Третий — на третий.

Пример:

SELECT TRANSLATE('abc', 'abc', 'xyz') AS result;

Получится:

result
xyz

Разберём:

• a заменился на x;
• b заменился на y;
• c заменился на z.

Если в строке встречаются символы, которых нет в from_set, они остаются как есть.

SELECT TRANSLATE('a-b-c', 'abc', 'xyz') AS result;

Результат:

result
x-y-z

Дефисы остались на месте, потому что символа - нет в наборе abc.

TRANSLATE обрабатывает каждый символ отдельно

Это ключевой момент.

SELECT TRANSLATE('cab', 'abc', 'xyz') AS result;

Результат:

result
zxy

Почему так?

• c заменился на z;
• a заменился на x;
• b заменился на y.

Функции не важно, в каком порядке символы стоят в исходной строке. Она просто смотрит на каждый символ отдельно и проверяет, есть ли он в from_set.

Ещё пример:

SELECT TRANSLATE('banana', 'an', '12') AS result;

Результат:

result
b12121

Здесь:

• a превращается в 1;
• n превращается в 2;
• b не меняется.

Регистр имеет значение

TRANSLATE различает маленькие и большие буквы.

SELECT TRANSLATE('AaAa', 'a', 'x') AS result;

Результат:

result
AxAx

Маленькая a заменилась на x, а большая A осталась без изменений.

Если нужно обработать оба варианта, укажите оба символа:

SELECT TRANSLATE('AaAa', 'aA', 'xX') AS result;

Результат:

result
XxXx

Или сначала приведите строку к одному регистру:

SELECT TRANSLATE(LOWER('AaAa'), 'a', 'x') AS result;

Результат:

result
xxxx

Как удалить символы через TRANSLATE

Очень полезная особенность: если в to_set не хватает символа для замены, символ из from_set удаляется.

Самый простой пример:

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

Результат:

result
abc

Мы сказали: «Найди дефис». Но не дали символ, на который его нужно заменить. Поэтому дефис просто исчез.

Так можно быстро очищать строки от лишних символов.

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

SELECT TRANSLATE('+1 (555) 123-45-67', ' ()-+', '') AS phone_clean;

Результат:

phone_clean
15551234567

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

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

phone
+1 (555) 123-45-67
+1 555 123 45 67
1-555-123-45-67

А для проверки дублей вы хотите привести всё к одному виду:

SELECT
  id,
  TRANSLATE(phone, ' ()-+', '') AS phone_clean
FROM users;

Замена нескольких разделителей за один вызов

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

Нужно привести их к подчёркиванию.

Через REPLACE пришлось бы писать несколько вложенных вызовов:

SELECT REPLACE(REPLACE(REPLACE('a.b,c;d', '.', '_'), ',', '_'), ';', '_') AS result;

Работает, но выглядит тяжеловато.

Через TRANSLATE проще:

SELECT TRANSLATE('a.b,c;d', '.,;', '___') AS result;

Результат:

result
a_b_c_d

Здесь:

• . заменяется на _;
• , заменяется на _;
• ; заменяется на _.

Если все символы нужно заменить на один и тот же символ, просто повторите его в to_set столько раз, сколько символов указано в from_set.

Пример: сделать статус красивее для экспорта

Представим, что в базе статусы заказов хранятся в техническом виде:

status
new_order
payment_waiting
ready_to_ship

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

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

Результат будет примерно такой:

status_label
new order
payment waiting
ready to ship

Это ещё не идеальный красивый текст, но уже лучше для отчёта или экспорта.

Если нужно ещё привести первую букву каждого слова к верхнему регистру, можно добавить INITCAP:

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

Результат:

status_label
New Order
Payment Waiting
Ready To Ship

Пример: подготовить часть slug

slug — это строка для URL: обычно маленькими буквами, без пробелов и странной пунктуации.

Например, из названия:

SQL Basics: Joins / Aggregates

можно сделать:

sql-basics:-joins---aggregates

Простой вариант через LOWER и TRANSLATE:

SELECT TRANSLATE(LOWER('SQL Basics: Joins / Aggregates'), ' /', '--') AS slug_part;

Результат:

slug_part
sql-basics:-joins---aggregates

Здесь:

• пробел заменяется на дефис;
• / заменяется на дефис;
• буквы приводятся к нижнему регистру через LOWER.

Можно добавить ещё символы:

SELECT TRANSLATE(LOWER('SQL Basics: Joins / Aggregates.'), ' /:.', '----') AS slug_part;

Результат:

slug_part
sql-basics--joins---aggregates-

Такой подход подходит для простой нормализации, но полноценная генерация slug часто требует дополнительных шагов: убрать повторяющиеся дефисы, обрезать дефисы по краям, обработать не-ASCII-символы.

TRANSLATE хорош именно как один из шагов очистки.

Если from_set длиннее to_set

Допустим, мы хотим заменить пробел и слэш на дефис, а точку удалить.

Можно написать так:

SELECT TRANSLATE('a b/c.d', ' /.', '--') AS result;

Результат:

result
a-b-cd

Разберём соответствие:

Символ из from_set	Символ из to_set	Что происходит
пробел	-	заменяется на дефис
/	-	заменяется на дефис
.	нет пары	удаляется

То есть если from_set длиннее, чем to_set, лишние символы из from_set не заменяются, а удаляются.

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

Например:

SELECT TRANSLATE('a.b,c;d', '.,;', '__') AS result;

Результат:

result
a_b_cd

Точка и запятая заменились на _, а точка с запятой удалилась, потому что для неё не хватило третьего символа в to_set.

Если вы хотели заменить все три разделителя на _, нужно написать так:

SELECT TRANSLATE('a.b,c;d', '.,;', '___') AS result;

Повторы в from_set

Символы в from_set лучше делать уникальными.

Если один и тот же символ указан несколько раз, PostgreSQL учитывает его первое вхождение.

SELECT TRANSLATE('a', 'aa', 'xy') AS result;

Результат:

result
x

Почему не y?

Потому что первая a в from_set соответствует x. Повторная a уже не меняет правило.

Поэтому наборы для TRANSLATE лучше писать аккуратно и без дублей.

Плохо:

SELECT TRANSLATE(name, ' //--', '----') AS normalized
FROM employees;

Лучше:

SELECT TRANSLATE(name, ' /-', '---') AS normalized
FROM employees;

Так проще понять, какие символы реально заменяются.

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

REPLACE заменяет подстроку целиком.

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

Результат:

result
a_b_c

Здесь всё просто: нашли каждую точку и заменили на подчёркивание.

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

SELECT REPLACE(REPLACE(REPLACE('a.b,c;d', '.', '_'), ',', '_'), ';', '_') AS result;

Через TRANSLATE это делается одним вызовом:

SELECT TRANSLATE('a.b,c;d', '.,;', '___') AS result;

Результат:

result
a_b_c_d

Но TRANSLATE не умеет заменять целые слова или многосимвольные куски.

Например, если нужно заменить cat на dog, нужен REPLACE:

SELECT REPLACE('cat and cat', 'cat', 'dog') AS result;

Результат:

result
dog and dog

А TRANSLATE для такой задачи не подходит. Он будет смотреть на отдельные символы c, a, t, а не на слово cat.

Запомнить можно так:

REPLACE — для подстрок.
TRANSLATE — для наборов отдельных символов.

Когда TRANSLATE особенно удобен

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

Например:

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

Пример с артикулом:

SELECT
  id,
  TRANSLATE(product_code, ' -.', '') AS code_clean
FROM products;

Если было AB-12. 45, получится AB1245.

Пример с несколькими разделителями:

SELECT
  id,
  TRANSLATE(raw_value, ',;|', '---') AS normalized_value
FROM imports;

Если было a,b;c|d, получится a-b-c-d.

Использование в WHERE и индексах

Иногда хочется искать по очищенному значению.

Например, в таблице телефоны лежат в разном формате, а пользователь ищет чистые цифры:

SELECT
  id,
  name,
  phone
FROM users
WHERE TRANSLATE(phone, ' ()-+', '') = '15551234567';

Логика понятная: очистили телефон в запросе и сравнили с чистым значением.

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

Если обычный индекс построен по колонке phone, то выражение TRANSLATE(phone, ' ()-+', '') не равно просто phone. Базе может быть трудно использовать обычный индекс.

Для частых поисков можно создать функциональный индекс:

CREATE INDEX users_phone_clean_idx
ON users (TRANSLATE(phone, ' ()-+', ''));

После этого PostgreSQL сможет использовать индекс для запросов с таким же выражением:

SELECT
  id,
  name,
  phone
FROM users
WHERE TRANSLATE(phone, ' ()-+', '') = '15551234567';

Главное условие: выражение в запросе должно совпадать с выражением в индексе.

Если такая нормализация используется часто, лучше не размазывать её по десяткам запросов. Хорошая практика — вынести правило в одно место: во VIEW, generated column, staging-таблицу или отдельный шаг подготовки данных.

Как не размазать правило по проекту

Допустим, вы в нескольких отчётах очищаете телефон так:

TRANSLATE(phone, ' ()-+', '')

Сначала это выглядит безобидно. Потом один аналитик добавляет точку в список удаляемых символов, второй забывает добавить плюс, третий меняет правило только в одном отчёте.

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

Чтобы такого не было, нормализацию лучше закрепить в одном месте.

Например, через представление:

CREATE VIEW users_clean AS
SELECT
  id,
  name,
  phone,
  TRANSLATE(phone, ' ()-+', '') AS phone_clean
FROM users;

Теперь отчёты могут обращаться к готовому полю:

SELECT
  id,
  name,
  phone_clean
FROM users_clean;

Или можно использовать generated column, если очищенное значение нужно хранить рядом с исходным:

ALTER TABLE users
ADD COLUMN phone_clean text
GENERATED ALWAYS AS (TRANSLATE(phone, ' ()-+', '')) STORED;

Так правило становится частью схемы, а не случайной строкой в отчёте.

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

Если исходная строка равна NULL, результат тоже будет NULL.

SELECT TRANSLATE(NULL, 'abc', 'xyz') AS result;

Результат:

result
NULL

Это обычное поведение SQL-функций: неизвестное значение на входе даёт неизвестное значение на выходе.

Пустая строка остаётся пустой строкой:

SELECT TRANSLATE('', 'abc', 'xyz') AS result;

Результат:

result
пустая строка

Строка без целевых символов не меняется:

SELECT TRANSLATE('hello', 'abc', 'xyz') AS result;

Результат:

result
hello

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

Особенности в разных СУБД

В PostgreSQL TRANSLATE работает так, как мы разобрали:

SELECT TRANSLATE('a.b,c;d', '.,;', '___') AS result;

Можно заменять символы и удалять их, если to_set короче from_set.

В Oracle функция тоже есть, но есть важная особенность: пустая строка там трактуется как NULL. Поэтому приёмы с пустым to_set могут вести себя не так, как в PostgreSQL. Для удаления символов в Oracle обычно используют непустой to_set, а удаляемые символы оставляют без пары.

В MySQL и MariaDB функции TRANSLATE в привычном виде нет. Обычно используют вложенные REPLACE или регулярные выражения, если версия поддерживает нужные функции.

В ClickHouse есть translate и translateUTF8.

SELECT translateUTF8(name, 'aeiou', 'AEIOU')
FROM users;

Для строк с не-ASCII-символами важно выбирать вариант, который корректно работает с UTF-8. Иначе можно получить неприятные сюрпризы на многобайтовых символах.

На что обратить внимание при переносе запросов

Не переносите логику с TRANSLATE между базами вслепую.

Проверьте хотя бы маленький набор тестовых строк:

value
NULL
пустая строка
abc
a.b,c;d
строка с не-ASCII
строка без целевых символов

Особенно важны крайние случаи:

• from_set длиннее to_set;
• в from_set есть повторяющиеся символы;
• исходная строка равна NULL;
• исходная строка пустая;
• в строке есть не-ASCII-символы;
• результат используется как ключ или публичный идентификатор.

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

Главное

TRANSLATE заменяет символы по позициям.

SELECT TRANSLATE('abc', 'abc', 'xyz') AS result;

a меняется на x, b — на y, c — на z.

Символы, которых нет в from_set, остаются без изменений.

SELECT TRANSLATE('a-b-c', 'abc', 'xyz') AS result;

Если to_set короче from_set, символы без пары удаляются.

SELECT TRANSLATE('+1 (555) 123-45-67', ' ()-+', '') AS phone_clean;

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

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

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

Если нужно заменить слово — берите REPLACE.
Если нужно заменить или удалить несколько отдельных символов за один проход — берите TRANSLATE.

Для частых фильтров вида TRANSLATE(col, ...) = value обычный индекс по колонке может не помочь. В PostgreSQL под такие случаи нужен функциональный индекс по тому же выражению.

И последнее: если результат TRANSLATE становится ключом, кодом или частью URL, обязательно зафиксируйте правило. Какие символы заменяются, какие удаляются, что делать с пустой строкой, NULL, дублями и не-ASCII. Тогда строковая магия не превратится в тихий источник багов.