Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

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.

Syntax

Copy
TRANSLATE ( expression, characters_to_replace, characters_to_substitute )

Arguments

expression

The expression to be translated.

characters_to_replace

A string containing the characters to be replaced.

characters_to_substitute

A string containing the characters to substitute.

Return Type

VARCHAR

Examples

The following example replaces several characters in a string:

Copy
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:

Copy
select email, translate(email, '@', '.') as obfuscated_email from users limit 10; email obfuscated_email ------------------------------------------------------------------------------------------- Etiam.laoreet.libero@sodalesMaurisblandit.edu Etiam.laoreet.libero.sodalesMaurisblandit.edu amet.faucibus.ut@condimentumegetvolutpat.ca amet.faucibus.ut.condimentumegetvolutpat.ca turpis@accumsanlaoreet.org turpis.accumsanlaoreet.org ullamcorper.nisl@Cras.edu ullamcorper.nisl.Cras.edu arcu.Curabitur@senectusetnetus.com arcu.Curabitur.senectusetnetus.com ac@velit.ca ac.velit.ca Aliquam.vulputate.ullamcorper@amalesuada.org Aliquam.vulputate.ullamcorper.amalesuada.org vel.est@velitegestas.edu vel.est.velitegestas.edu dolor.nonummy@ipsumdolorsit.ca dolor.nonummy.ipsumdolorsit.ca et@Nunclaoreet.ca et.Nunclaoreet.ca
The following example replaces spaces with underscores and strips out periods for all values in a column:
Copy
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