PostgreSQL translate() Function

The PostgreSQL translate() function translates a specified string according to the specified translation relation. A translation relation is a one-to-one correspondence between characters in two strings.

translate() Syntax

This is the syntax of the PostgreSQL translate() function:

translate(string, from_set, to_set)

Parameters

string

Required. The string to translate.

from_set

Required. The string including characters to translate.

to_set

Required. The string that is the set of characters to translate to. The characters in from_set and the characters in to_set form a one-to-one translation relationship according the order of characters.

Return value

The PostgreSQL translate() function returns a translated string string with all characters specified in from_set be translated to the corresponding characters in to_set.

translate() Examples

This example shows how to use the translate() function to translate a string:

SELECT translate('xabcdef', 'abcd', '123');

 translate
-----------
 x123ef

Let’s take a look at the executing steps of translate('xabcdef', 'abcd', '123'):

1. from_set is abcd. It tells us the four characters (a, b, c and d) are going to be translated.

2. to_set is 123. The following translation relationships between from_set and to_set are established:

   • a will be translated into 1
   • b will be translated into 2
   • c will be translated into 3
   • d will be translated to '', that is the empty string

3. The translation process is as follows:

   1. x is not in from_set, so x is reserved.
   2. a is in from_set, so a is translated into 1.
   3. b is in from_set, so b is translated into 2.
   4. c is in from_set, so c is translated into 3.
   5. d is in from_set, so d is translated into ''.
   6. e is not in from_set, so e is reserved.
   7. f is not in from_set, so f is reserved.

4. The result of the translation was x123ef and translate() returned it.