TRANSLATE function - Amazon Redshift

TRANSLATE function

For a given expression, replaces all occurrences of specified characters with specified substitutes. Existing characters are mapped to replacement characters by their positions in the characters_to_replace and characters_to_substitute arguments. If more characters are specified in the characters_to_replace argument than in the characters_to_substitute argument, the extra characters from the characters_to_replace argument are omitted in the return value.

TRANSLATE is similar to the REPLACE function and the REGEXP_REPLACE function, except that REPLACE substitutes one entire string with another string and REGEXP_REPLACE lets you search a string for a regular expression pattern, while TRANSLATE makes multiple single-character substitutions.

If any argument is null, the return is NULL.


TRANSLATE ( expression, characters_to_replace, characters_to_substitute )



The expression to be translated.


A string containing the characters to be replaced.


A string containing the characters to substitute.

Return type



The following example replaces several characters in a string:

select translate('mint tea', 'inea', 'osin'); translate ----------- most tin

The following example replaces the at sign (@) with a period for all values in a column:

select email, translate(email, '@', '.') as obfuscated_email from users limit 10; email obfuscated_email -------------------------------------------------------------------------------------------

The following example replaces spaces with underscores and strips out periods for all values in a column:

select city, translate(city, ' .', '_') from users where city like 'Sain%' or city like 'St%' group by city order by city; city translate --------------+------------------ Saint Albans Saint_Albans Saint Cloud Saint_Cloud Saint Joseph Saint_Joseph Saint Louis Saint_Louis Saint Paul Saint_Paul St. George St_George St. Marys St_Marys St. Petersburg St_Petersburg Stafford Stafford Stamford Stamford Stanton Stanton Starkville Starkville Statesboro Statesboro Staunton Staunton Steubenville Steubenville Stevens Point Stevens_Point Stillwater Stillwater Stockton Stockton Sturgis Sturgis