How to use REPLACE function in Snowflake

Replace is a column transformation function in Snowflake which replaces substrings, in its simplest form all you need to run is select replace(subject,pattern,replacement) from table. In this post I will walk through what it’s useful for and how to use it in your database.

Snowflake replace, what is it?

Replace is a function which takes a subject string and replaces a defined substring pattern with a new substring.

How can you use replace in Snowflake?

The syntax for using replace in Snowflake is:

replace( subject , pattern , replacement )

Where the arguments are:

• subject - The string or column where the replacement will take place
• pattern - The pattern which will be replaced, this can either be a string or a column
• replacement - The new pattern which will be inserted, this can either be a string or a column

There are a few things that you should be aware of when using the replace function:

• This function only works columns or values that are a string datatype
• The replacement argument isn’t required, if it is left blank then the pattern is simply removed
• If you provide null as one of the arguments then the function will return null

Example of using replace function in Snowflake

Replace is a function which few know about but one that can be very powerful when used in the right situations. Here is a simple example of you could use replace to fix for a spelling mistake in one of your columns:

/*
|  product  | category |
|-----------|----------|
| Milk      | Diary    |
| Butter    | Diary    |
| Cream     | Diary    |
| Margarine | Diary    |
*/

select 
product, 
category,
replace(category,"ia","ai") as category_correct_spelling
from prodcuts

/*
|  product  | category | category_correct_spelling |
|-----------|----------|---------------------------|
| Milk      | Diary    | Dairy                     |
| Butter    | Diary    | Dairy                     |
| Cream     | Diary    | Dairy                     |
| Margarine | Diary    | Dairy                     |
*/

Related articles

Snowflake coalesce
Snowflake listagg
Snowflake concat
Snowflake substring
Randomly sample rows in Snowflake

References

Snowflake replace documentation