loading..
Русский    English
19:17
листать

Функция TRANSLATE

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

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

  1. begin tran;
  2. SELECT model, maker FROM product WHERE maker IN('A','B');
  3. UPDATE product SET maker = CASE maker WHEN 'B' THEN 'A' WHEN 'A' THEN 'B' ELSE maker END;
  4. SELECT model, maker FROM product WHERE maker IN('A','B');
  5. rollback;

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

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

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

model    maker
1121    B
1232    A
1233    A
1276    A
1298    A
1401    A
1408    A
1750    B
1752    A

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

model    maker
1121    A
1232    B
1233    B
1276    B
1298    B
1401    B
1408    B
1750    A
1752    B

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

  1. begin tran;
  2. SELECT model, maker FROM product WHERE maker IN('A','B');
  3. UPDATE product SET maker = TRANSLATE(maker, 'BA', 'AB');
  4. SELECT model, maker FROM product WHERE maker IN('A','B');
  5. rollback;

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

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

Консоль
Выполнить
  1. SELECT translate('aaAbB','ab','BA');

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

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

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

Пример:

Консоль
Выполнить
  1. WITH encrypt AS
  2. (SELECT name, translate(name,'abcdefghijklmnopqrstuvwxyz',
  3.                                     'laksjdhfgqpwoeirutymznxbcv') encrypt_name
  4. FROM passenger)
  5. SELECT * FROM encrypt
  6. -- Чтобы расшифровать, достаточно поменять аргументы местами
  7. UNION ALL
  8. SELECT encrypt_name,
  9. translate(encrypt_name,'laksjdhfgqpwoeirutymznxbcv',
  10.                                    'abcdefghijklmnopqrstuvwxyz') original_name
  11. FROM encrypt;

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

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

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

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

Консоль
Выполнить
  1. WITH encrypt AS
  2. (SELECT name, translate(name,'abcdefghijklmnopqrstuvwxyz',
  3.                                    'laksjdjfgqpwoeirutymznxbcv') encrypt_name
  4. FROM passenger)
  5. SELECT * FROM encrypt
  6. WHERE name = 'George Clooney'
  7. -- Пытаемся расшифровать
  8. UNION ALL
  9. SELECT encrypt_name,
  10. translate(encrypt_name,'laksjdjfgqpwoeirutymznxbcv',
  11.                                    'abcdefghijklmnopqrstuvwxyz') original_name
  12. FROM encrypt
  13. WHERE name = 'George Clooney';

name    encrypt_name
George Clooney          jjitjj kwiiejc
jjitjj kwiiejc          eeoree clooney

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

Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
https://it-hand.ru
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.