Syntax

The following illustrates the syntax of the PostgreSQLTRANSLATE() function:

TRANSLATE(string,from,to)

Arguments

The PostgreSQLTRANSLATE() function accepts three arguments:

1)string
is a string subjected to translation.

2)from
is a set of characters in the first argument (string) that should be replaced.

3)to
is a set of characters that replaces thefrom in the string.

Notice that iffrom is longer thanto, theTRANSLATE() function removes the occurrences of the extra characters infrom.

Return value

The PostgreSQLTRANSLATE() function returns a string with the characters in the one set of characters replaced by another set of characters.

Examples

Let’s see some examples of using theTRANSLATE() function to understand how it works.

1) Basic TRANSLATE() function example

See the following statement:

SELECT TRANSLATE('12345','134','ax')

In this example:

• The character'1' in string'12345' is substituted by character'a', The character'3' in the string'12345' is substituted by the character'x'.
• Because the string'134' has more characters than the string'ax', theTRANSLATE() function removes the extra character in the string'134', which is'4', from the string'12345'.

The following illustrates the result:

translate----------- a2x5(1 row)

2) Single character replacement

The following example shows how to use theTRANSLATE() function to replace comma (,) with a semi-colon (;) in a comma-separated values list.

SELECT TRANSLATE('apple,orange,banana', ',', ';');

Here is the output:

translate--------------------- apple;orange;banana(1 row)

3) Encrypting and decrypting a message

The following example shows how to use theTRANSLATE() function to encrypt a message:

SELECT TRANSLATE('a secret message',                 'abcdefghijklmnopqrstuvxyz',                 '0123456789acwrvyuiopkjhbq');

Here is the output:

translate------------------ 0 o42i4p w4oo064(1 row)

You can also decrypt the message'0 o42i4p w4oo064' using the function:

SELECT TRANSLATE('0 o42i4p w4oo064',                     '0123456789acwrvyuiopkjhbq',                     'abcdefghijklmnopqrstuvxyz');

Hence the output is:

translate------------------ a secret message(1 row)

In this tutorial, you have learned how to use the PostgreSQLTRANSLATE() function to substitute characters in a set with another, one-to-one, in a single operation.

Last updated onJanuary 29, 2024

On this page

• Syntax
• Arguments
• Return value
• Examples