Snowflake TRANSLATE Function, Usage and Examples

Post author:Vithal S
Post last modified:September 8, 2020
Post category:Snowflake
Reading time:5 mins read

There are many situations in data warehouse where you need to replace the one character with another. For example, replace special character such as symbol in the input expression with a space or remove it. In this article, we will check how to use the Snowflake TRANSLATE function to replace characters with some examples.

Snowflake TRANSLATE Function

In general, you can use translate function to translate or replace one or more characters into another set of characters.

Snowflake supports the translate function, which performs the same job as translate function in any other relational databases such as Redshift, Netezza, etc.

Snowflake TRANSLATE Syntax

Following is the syntax of TRANSLATE function.

TRANSLATE( <string>, <sourceAlphabet>, <targetAlphabet> )

Where,

string – input string or expression
sourceAlphabet – A string with all characters that are modified by this function.
targetAlphabet – A string with all characters that are used to replace characters from the sourceAlphabet

This function converts all the characters in the input string that also occur in sourceAlphabet to the corresponding characters in targetAlphabet. If sourceAlphabet is longer than targetAlphabet, occurrences of the extra characters in the input sourceAlphabet are removed from the input string.

Snowflake TRANSLATE Examples

There are many use cases of translate function.

For example, translate ‘$’ to ‘B’, ‘#’ to ‘D’, ‘@’ to ‘F’ and remove ‘*’.

select TRANSLATE('A$C#E@**', '$#@*', 'BDF') as TRANSLATE_EXAMPLE;
+-------------------+
| TRANSLATE_EXAMPLE |
|-------------------|
| ABCDEF            |
+-------------------+

Another translate example would be to trim the string for any blank values.

For example, consider following SQL statement to remove a blank value from a string.

select TRANSLATE('Start  ABC     end', ' ', '') as TRANSLATE_EXAMPLE;
+-------------------+
| TRANSLATE_EXAMPLE |
|-------------------|
| StartABCend       |
+-------------------+

When Should you Use TRANSLATE Function?

As explained in the examples, the TRANSLATE function can be useful if you need to replace all occurrences of each character specified, regardless of their order within the original string.

If you have requirement to replace the sub-string within a given input string expression then REPLACE function will be more useful.