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
replacementargument isn’t required, if it is left blank then the
patternis simply removed
- If you provide
nullas one of the arguments then the function will return
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 | */