How to Use Translate Function in Snowflake?

The Snowflake TRANSLATE function returns a string with all occurrences of each character in a string replaced by its corresponding character in the replacement string. For example, you can use Snowflake translate function to replace ‘#’ with a ‘@’ symbol and remove ‘$’ from input string in one operation. The translate is one of the commonly used Snowflake string functions. In this article, we will check how to use the Translate function in the Snowflake cloud database.

Translate Function in Snowflake

As discussed in the previous example, the Snowflake translate function allows you to make many single-character, one-to-one translations or substitutions in one operation.

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

What is Snowflake TRANSLATE Function Syntax?

Following is the syntax of translate function.

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

Where,

string – input string or expression
sourceAlphabet – A string contains a characters that needs to be replaced.
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. However, 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. One can use the translate function based on the requirements.

For example, replace ‘$’ with ‘S’ and ‘@’ with ‘a’ respectively.

select TRANSLATE('$nowfl@ke', '$@*', 'Sa') as sn_translate;
+--------------+
| SN_TRANSLATE |
|--------------|
| Snowflake    |
+--------------+

Snowflake Translate to Remove Blank Values from String

Another translate example is to remove any blank values from string. For instance, consider following SQL statement to remove a blank value from a string.

select TRANSLATE('Snowflake      Database', ' ', '') as sn_translate;
+-------------------+
| SN_TRANSLATE      |
|-------------------|
| SnowflakeDatabase |
+-------------------+

Snowflake Translate function to Remove Newline Character and Junk Characters from String

Following translate example removes a newline (\n) and ‘#’ Junk character of an expression with a space.

select TRANSLATE('Snowflake\nCloud#Database', '\n#', '  ') as sn_translate;

+--------------------------+
| SN_TRANSLATE             |
|--------------------------|
| Snowflake Cloud Database |
+--------------------------+

When Should you Use Snowflake Translate and Replace Function? – Snowflake Translate vs Replace Function

The TRANSLATE provides functionality provided by the REPLACE function. However, Snowflake REPLACE function lets you substitute a single string for another single string, as well as remove character strings. And Snowflake TRANSLATE lets you make several single-character, one-to-one substitutions in one operation. So, use either translate or replace function as per your requirements.