PostgreSQL TRANSLATE Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL TRANSLATE() function to replace all occurrences of characters in a set with corresponding characters from another set.

Overview of the PostgreSQL TRANSLATE function #

The TRANSLATE() function allows you to replace all occurrences of a set of characters in a string with corresponding characters from another set of characters.

Here’s the syntax of the TRANSLATE() function:

TRANSLATE(string, from_set, to_set)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The TRANSLATE() function takes three parameters:

• string: The input string to perform the character replacements.
• from_set: A string that contains a set of characters to be replaced in the input string.
• to_set: A string that contains replacement characters for the characters in the from_set.

The TRANSLATE() function returns a new string with all characters in the from_set replaced by the corresponding characters in the to_set.

If the length of the from_set is greater than the length of the to_set, the TRANSLATE() function will remove the occurrences of the extra characters in from_set.

Basic PostgreSQL TRANSLATE function #

The following statement uses the TRANSLATE() function to replace characters in 'abc' set with the corresponding characters in '12' set:

SELECT TRANSLATE('abcdef', 'abc', '12') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

result
--------
12defCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• The function TRANSLATE() replaces the character 'a' and 'b' in the 'abcdef' with the character '1' and '2'.
• The string 'ABC' has a length of 3, more than the length of the string '12', one character. Therefore, the TRANSLATE() function removes the character 'c' in the 'abcdef'.
• The result string is '12def'.

Performing a single character replacement #

The following example uses the TRANSLATE() function to replace the character ',' by ';' in a string:

SELECT
  TRANSLATE('sql, postgres, postgresql', ',', ';') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

          result
---------------------------
 sql; postgres; postgresqlCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Encrypting and decrypting messages #

The following example uses the TRANSLATE() function to encrypt a message:

SELECT
  TRANSLATE(
    'pgtutorial.com',
    'bplchjuyrkedmxfaiotgqvzns',
    'tefmgrolbpuzxhkdqvynascji'
  ) encrypted_message;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 encrypted_message
-------------------
 enyoyvbqdf.mvxCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the TRANSLATE() function replaces characters from the first set with the characters from the second set in the string 'pgtutorial.com' .

You can decrypt the message using the TRANSLATE function with the same key as follows:

SELECT
  TRANSLATE(
    'enyoyvbqdf.mvx',
    'tefmgrolbpuzxhkdqvynascji',
    'bplchjuyrkedmxfaiotgqvzns'
  ) message;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    message
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the TRANSLATE() function to replace all occurrences of a set of characters in a string with corresponding characters from another set of characters.