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

Пусть нам требуется поменять местами производителей 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');

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;

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';

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".