PostgreSQL has a translate() function which replaces certain characters in a string with certain other characters.

We specify the characters in the string that we want to replace, as well as the characters that we want to replace them with. The function then performs the translation and returns the string with the replaced characters.

Example

Here’s an example to demonstrate:

SELECT translate('Payout Ratio', 'yt', 'sd');

Result:

Pasoud Radio

So translate() is similar, but still quite different, to the replace() function. The replace() function substitutes a single string for another single string, and it can also remove character strings. The translate() function on the other hand lets us make several single-character, one-to-one substitutions in a single operation.

Here’s another example that replaces square and curly braces with parentheses:

SELECT translate('7*[51+27]/{19-23}', '[]{}', '()()');

Result:

7*(51+27)/(19-23)

That’s the equivalent of using replace() like this:

SELECT
    replace
    (
        replace
        (
            replace
            (
                replace
                (
                    '7*[51+27]/{19-23}',
                    '[',
                    '('
                ),
                ']',
                ')'
            ),
            '{',
            '('
        ),
        '}',
        ')'
    );

Result:

7*(51+27)/(19-23)

Deleting Characters

If we specify more matching characters than replacement characters (i.e. if the second argument is longer than the third), then any matching characters that don’t have a corresponding replacement are deleted:

SELECT translate('Payout Ratio', 'yt', 's');

Result:

Pasou Raio

This is based on the first example above, but in this example I removed one of the characters from the third argument. Therefore we have two matching characters but only one of them (y) has a corresponding replacement character (s). The other one (t) has no corresponding replacement character and so all occurrences of t in the string are deleted.

Non-Matching Characters

If our second argument contains characters that aren’t in the first argument, those characters are ignored:

SELECT translate('Payout Ratio', 'xyz', '789');

Result:

Pa8out Ratio

In this case, only the second character matched one in the original string and so only that one was replaced. The other two (x and z) had no effect because they weren’t in the original string.

Null Arguments

If any argument is null, then null is returned:

SELECT 
    translate(null, 'xyz', '789') AS "1",
    translate('Payout Ratio', null, '789') AS "2",
    translate('Payout Ratio', 'xyz', null) AS "3",
    translate(null, null, null) AS "4";

Result:

  1   |  2   |  3   |  4   
------+------+------+------
 null | null | null | null