Функция TRANSLATE

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

Мы могли бы это сделать следующим образом:

begin tran;
select model, maker from product where maker in('A','B');
update product set maker = case maker when 'B' then 'A' when 'A' then 'B' else maker end;
select model, maker from product where maker in('A','B');
rollback;

Для демонстрации сначала мы выводим состояние строк таблицы Product, относящихся к производителям A и B, до обновления, а затем - после.

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

До обновления

После обновления

То же самое мы могли бы сделать с помощью функции TRANSLATE, которая появилась в SQL Server 2017:

begin tran;
select model, maker from product where maker in('A','B');
update product set maker = TRANSLATE(maker, 'BA', 'AB');
select model, maker from product where maker in('A','B');
rollback;

Эквивалентны ли эти коды? Нет! Дело в том, что функция TRANSLATE не только поменяет местами производителей A и B, но заменит во ВСЕХ именах производителей каждую букву A (независимо от регистра) на букву B и каждую букву B (независимо от регистра) на букву A.

Выполните следующий оператор, чтобы проверить независимость от регистра:

select translate('aaAbB','ab','BA');

Итак, функция TRANSLATE имеет три аргумента: выражение, к которому применяется преобразование, и два списка символов (одинаковой длины!).

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

С помощью функции TRANSLATE можно реализовать простейший способ шифрования, когда каждая буква в исходном тексте меняется на некоторую другую.

Пример:

with encrypt as
(select name, translate(name,'abcdefghijklmnopqrstuvwxyz',
                                    'laksjdhfgqpwoeirutymznxbcv') encrypt_name
from passenger)
select * from encrypt
-- Чтобы расшифровать, достаточно поменять аргументы местами
union all
select encrypt_name,
translate(encrypt_name,'laksjdhfgqpwoeirutymznxbcv',
                                   'abcdefghijklmnopqrstuvwxyz') original_name
from encrypt;

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

Дело в том, что мы можем заменить несколько разных символов одним.

В целях демонстрации возьмем для удобства только одну строку из таблицы с именем  George Clooney. И будем менять две буквы “e” и “g” на одну и ту же букву “j”.

Остальные символы в последовательности оставим без изменений. Тогда наш пример можно переписать так:

with encrypt as
(select name, translate(name,'abcdefghijklmnopqrstuvwxyz',
                                   'laksjdjfgqpwoeirutymznxbcv') encrypt_name
from passenger)
select * from encrypt
where name = 'George Clooney'
-- Пытаемся расшифровать
union all
select encrypt_name,
translate(encrypt_name,'laksjdjfgqpwoeirutymznxbcv',
                                   'abcdefghijklmnopqrstuvwxyz') original_name
from encrypt
where name = 'George Clooney';

Как можно увидеть, при расшифровке была использована первая буква “j” в списке и, соответственно, заменена на “e”.