Replace Function in Snowflake

The Replace function in Snowflake is one of the commonly used functions in string manipulations. For example, you can use the replace function to remove the unwanted or junk characters from your string. This function is similar to the translate function available in Snowflake. We will discuss about translate function in another post. For now, let us continue with the replace function, its syntax and some examples.

In general, replace function in SQL replaces each instance of a pattern in the input string with the value in the string replacement.

Snowflake replace function removes all occurrences of a specified substring, and optionally replaces them with another string. The function will simply delete all the occurrences if the replacement string is not provided.

The syntax and usage is same as the replace function in other relational databases or BigData framework Spark SQL.

Snowflake REPLACE Syntax

Following is the syntax of replace function.

REPLACE( <string> , <pattern> [ , <replacement> ] )

Where,

string – is the input string or an expression.
pattern – This is the substring that you want to replace.
replacement – This is an optional string as a pattern replacement.

The replace function will replace all occurrences with the replacement string in an input string. The Snowflake will delete the pattern from input if you omit replacement string.

Snowflake also provides the regular expression function regex_replace if you want to use regular expression to search the pattern.

Snowflake Replace Examples

The replace function is one of the important functions used to manipulate the string. This function plays critical role in data cleansing.

For example, remove hash sign before applying SUM function.

select SUM(REPLACE(col1, '#')) as total_val
from (select '#1000' as col1
union all select '#1200' as col1) as tmp;
+-------------+
| TOTAL_MONEY |
|-------------|
|        2200 |
+-------------+

Another example would be to replace sub string with replacement string.

For example, replace all occurrences of example with gmail in the input string.

select REPLACE('firstname.lastname@example.com', 'example', 'gmail') as email;
+------------------------------+
| EMAIL                        |
|------------------------------|
| firstname.lastname@gmail.com |
+------------------------------+

When You Should Use Snowflake Replace Function?

You should use replace function when your requirement is to replace certain character or substring with fixed characters. You should use regex_replace if your searching pattern is regular expression.