TRANSLATE function

Let’s say we need to swap manufacturers A and B, i.e. those models that belong to manufacturer A, transfer to manufacturer B, and vice versa.

We could do this as follows:

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;

To demonstrate, we first print the state of the rows of the Product table belonging to manufacturers A and B before the update, and then after update.

To ensure that the database is not changed, we roll back the transaction at the end. The results are shown below.

Before the update

After the update

We could do the same thing using the TRANSLATE function, which was introduced in 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;

Are these codes equivalent? No! The problem is that TRANSLATE will not only swap manufacturers A and B, but it will replace every A (case-insensitive) in ALL manufacturer names with B, and every B (case-insensitive) with an A.

Execute the following statement to test case-insensitiveness:

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

So, the TRANSLATE function has three arguments: the expression to which the transformation is applied, and two lists of characters (of the same length!).

The result of the function is the expression obtained from the first argument, in which each character from the first list is replaced by a character from the second list, standing at the same position in the list.

With the help of the TRANSLATE function, you can implement the simplest encryption method, when each letter in the original text is replaced by some other one.

The example

with encrypt as
(
    select name, translate(name,'abcdefghijklmnopqrstuvwxyz',
                                 'laksjdhfgqpwoeirutymznxbcv') encrypt_name
    from passenger
)
select * from encrypt
-- To decrypt, just swap the arguments
union all
select encrypt_name,
       translate(encrypt_name,'laksjdhfgqpwoeirutymznxbcv',
                              'abcdefghijklmnopqrstuvwxyz') original_name
from encrypt;

However, this algorithm only works if the correspondence between two sequences of characters is one-to-one.

The point is that we can replace several different characters with one.

For the sake of demonstration, we will take for convenience only one row from the table with the name George Clooney. And we will change two letters “e” and “g” to the same letter “j”.

We will leave the rest of the characters in the sequence unchanged. Then our example can be rewritten as follows:

with encrypt as
(
    select name, translate(name,'abcdefghijklmnopqrstuvwxyz',
                                 'laksjdjfgqpwoeirutymznxbcv') encrypt_name
    from passenger
)
select * from encrypt
where name = 'George Clooney'
-- Trying to decrypt
union all
select encrypt_name,
       translate(encrypt_name,'laksjdjfgqpwoeirutymznxbcv',
                              'abcdefghijklmnopqrstuvwxyz') original_name
from encrypt
where name = 'George Clooney';

As you can see, the first letter “j” in the list was used during decoding and, accordingly, replaced with “e”.