TRANSLATE() Function in PostgreSQL

In PostgreSQL, the translate() function is used to replace a set of characters in a string with another set of characters. It works by mapping each character in the first set to the corresponding character in the second set. This function is particularly useful for character-by-character replacement.

• Translate() function to replace a pattern of string in PostgreSQL
• Translate() function to replace Vowels of string in Postgres
• Translate() function to remove Vowels of string in Postgres

Syntax of Translate() function in PostgreSQL:

• string: The original string or column to be processed.
• from_set: A set of characters to be replaced.
• to_set: A set of characters to replace the characters in from_set

Examples of TRANSLATE() function:

Example 1: Basic Character Replacement in PostgreSQL

To replace characters one by we will be using Translate() function in the Below Example we have replaced ‘o’ with ‘1’, ‘L’ with ‘2’, and ‘e’ with ‘3’ in the string ‘PostgreSQL’:

 

SELECT TRANSLATE('PostgreSQL', 'oLe', '123') AS translated_string;

Output:

Example 2: Removing Characters (vowels) in PostgreSQL

To remove all vowels (‘a’, ‘e’, ‘i’, ‘o’, ‘u’) from the string ‘PostgreSQL’:

 

SELECT TRANSLATE('PostgreSQL', 'aeiou', '') AS translated_string;

Output:

Example 3: Using translate() in a Column of PostgreSQL Table

We will be using summerfruits table.

Removing a particular character from string in postgresql is accomplished by using translate() function as shown below

 

select *,translate(name,'e','') as new_name from summerfruits

Character ‘e’ is removed from Colour column. So the resultant table will be

Output:

Example 4: Using translate() in a Column to remove vowels of PostgreSQL Table

Removing a vowels from string column in postgresql is accomplished by using translate()  as shown below

 

select *,translate(name,'aeiou','') as new_name from summerfruits

All Vowels are removed from Colour column. So the resultant table will be

Output:

Example 5: Using translate() in a Column to replace vowels of PostgreSQL Table

replace  vowels in string column is accomplished by using translate() function as shown below

 

select *,translate(name,'aeiou','12345') as new_name from summerfruits

replacement works in sequence a is replaced by 1 , e is replaced by 2 and so on. So the resultant table will be

Output: