Snowflake REPLACE Function, Usage and Examples

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

Just like translate function, replace function is also one of the widely used string functions in Snowflake. The replace function is commonly used to manipulate the strings or expression. For example, replace the part of sub-string across the input string or expression. In this article, we will check replace function, its syntax, usage with some examples.

Snowflake REPLACE Function

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

Snowflake replace removes all occurrences of a specified substring, and optionally replaces them with another string. The syntax and usage is same as the replace function in other relational databases such as Netezza, Redshift, etc.

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.

This function replaces all occurrences of pattern in the input string with the replacement string. The function will simply remove the pattern if the replacement string is not provided.

You can also use Snowflake regex_replace to replace if your requirement is to replace substring pattern.

Snowflake Replace Examples

The replace function plays an important role when you want to manipulate a string to remove certain unwanted characters.

For example, remove currency sign before applying SUM function.

select SUM(REPLACE(col1, '$')) as total_money
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 yahoo with gmail in the input string.

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

When Should you Use REPLACE Function?

You should use replace when you need to replace all occurrences of a specific substring, exactly as it is written. For example, changing someone’s name to another name.

Use translate function if your requirement is to replace characters. There is a lot of difference between TRANSLATE and REPLACE when it comes to usage.