Snowflake replace function, a simple how to guide

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                     |
*/

Snowflake coalesce
Snowflake listagg
Snowflake concat
Snowflake substring

References

Snowflake replace documentation

Stephen Allwright

Stephen Allwright

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.
Oslo, Norway