PostgreSQL offers many functions that are operated on the strings. The PostgreSQL translate() function returns a string after replacing a set of strings from one main string with some set of characters specified. The translate() function takes in the main string in which characters have to be replaced, the set of characters that have to be replaced, and the set of characters with which the replacement will take place.

Let’s dive into the details of this translate function in PostgreSQL and see how it works.

What Does PostgreSQL translate() Function Do?

The translate() function in PostgreSQL does one-to-one translation of characters in a single operation. The basic syntax of the function is:

TRANSLATE(Main_String, String_from_set_to_replace, Replacement_String);

The function takes in 3 arguments:

● The first argument is the main string in which the translation will occur.

● The second argument is the set of strings that needs to be translated

● The third argument is the set of replacement strings that are to be replaced/ translated into the main string.

This function's return type is STRING. The function returns the main string in which the “String_from_set_to replace” and translated into ”Replacement_String”.If any of the parameters is NULL, the function will return NULL.

To make the concept more clear, let’s move towards the example.

Example

A simple example of the translate() function in PostgreSQL is illustrated below:

SELECT translate('abcdefghij', 'efg', '567');

In the above syntax:

- The main string is 'abcdefghij'

- The string to be replaced from the main string is “efg”. This string is to be translated into the replacement string.

- The replacement string is “567”.

- The translate() function does one-to-one translation of characters, which means that the:

• letter ”e” is translated into “5”.
• letter ”f” is translated into “6”
• letter ”g” is translated into “7”.

Hence, the output for the above query is:

We said that the function does the one-to-one translation. What if the characters that are being translated are repeated more than once in the main string? Let's see what happens in this scenario:

SELECT translate('abcdefghij_gfe', 'efg', '567');

The output of the above query advocated the statement.

It means that the characters are individually translated and wherever they occur, they will be translated into the respective translated character.

One thing is to note here if the “String_from_set_to replace” is longer than the ”Replacement_String”, the extra characters are removed from the main string if they are present in it. For instance:

SELECT translate('abcdefghij_gfe', 'efgabc', '567');

Now here in “String_from_set_to replace” the characters “abc” are extra so the characters will be removed from the main string giving the output as follows:

Replacing Single Character

Let’s consider the query in which a single character is replaced let's suppose we want to replace the white spaces with the commas in the string containing the names of students, we will write the query as:

SELECT translate('John Peter Alex Sarah', ' ', ',');

In the above query, the white spaces are replaced with commas. We will get the output as:

The names of students are separated by commas as they get replaced by the white spaces between them.

Encryption and Decryption of message

This function can also be used for encryption and decryption of messages. The message to be encrypted can be written as the main string and the elements to be encrypted and characters in which the message is to be encrypted are declared in the function. Let's consider the below query:

SELECT translate('This is translate function', 'abcdefghijklmnopqrstuvxyz', '0123456789zyxvutsrqponmlk');

The message will be encrypted as follows:

We can also similarly decrypt the message;

SELECT translate('T78q 8q pr0vqy0p4 5ov2p8uv', '0123456789zyxvutsrqponmlk', 'abcdefghijklmnopqrstuvxyz');

So this is how the translate() function in PostgreSQL works.

Conclusion

The translate() function in PostgreSQL replaces a set of characters in the main string with some translated characters. The main string, the set of characters to be replaced, and the replacement characters are passed as arguments into the function. Message encryption and decryption is one of the most crucial applications of this function.