PostgreSQL – TRANSLATE Function
The TRANSLATE function is used to replace all occurrences of a set of characters in a given string with corresponding characters from another set. This function is particularly useful for performing character-level substitutions or transformations.
The syntax of the TRANSLATE function is as follows:
TRANSLATE(string, from_set, to_set)
where string is the input string in which you want to perform the character replacements.
and from_set is a string containing the characters to be replaced in the input string.
and to_set is a string containing the replacement characters corresponding to the characters in the from_set.
Here’s an example of how you might use the TRANSLATE function in a SQL query:
SELECT TRANSLATE('Hello, World!', 'o', 'i') AS modified_string;
In this example, the result would be the string 'Helli, Wirld!', as all occurrences of the letter 'o' were replaced with 'i'.
Both from_set and to_set should have the same length. Each character in the from_set will be replaced with the corresponding character in the to_set.
SELECT TRANSLATE('abc123', '123', 'xyz') AS modified_string;
The result would be 'abcxyz', as each occurrence of '1' was replaced with 'x', '2' was replaced with 'y', and '3' was replaced with 'z'.
The TRANSLATE function performs character-level replacement, not substring-level replacement like the REPLACE function. If you need more complex transformations or conditional replacements, you might need to use other functions or techniques.