PostgreSQL translate function.

Preamble

The PostgreSQL translate function replaces a sequence of characters in a string with another set of characters. However, it replaces one character at a time.

For example, it replaces the first character in string_to_replace with the first character in replace_string. Then it will replace the second character in string_to_replace with the second character in replace_string, etc.

Syntax of the translate function in PostgreSQL

translate( string1, string_to_replace, replacement_string )

Parameters and function arguments

• string1 – String to replace the character sequence with another character set.
• string_to_replace – String that will search in string1.
• replacement_string – All characters in string_to_replace will be replaced by the corresponding characters in replace_string.

The translate function can be used in the following PostgreSQL versions

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Let’s look at some examples of the translate function to understand how to use the translate function in PostgreSQL.
For example:

SELECT translate('Google.com', 'o', 'A');

--Result: "GAAgle.cAm"

SELECT translate('Google.com', 'oe', 'ABC');

--Result: "GAAglB.cAm"

SELECT translate('Google.com', 'e.c', '456');

--Result: 'Googl456om'

Learn PostgreSQL Tutorial; Full Course for Beginners

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.