Snowflake snippets
How to create and use a SQL function in Snowflake
Let's say you have the following table GLOBALSUPERSTORE_ORDERS
SELECT *
FROM GLOBAL_SUPERSTORE.PUBLIC.GLOBALSUPERSTORE_ORDERS
LIMIT 100;
![Snowflake snippet 1](static/img/CookBook/Snowflake/IMG_001.png)
And you want to create a function to extract the year from the order_id column.
CREATE OR REPLACE FUNCTION ExtractYear(order_id varchar)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
TO_NUMBER(REPLACE(REGEXP_SUBSTR(order_id,'\-[0-9]{4}\-'),'-',''))
$$;
![Snowflake snippet 2](static/img/CookBook/Snowflake/IMG_002.png)
Then you can use the function in a select, as the following example.
SELECT ExtractYear(order_id),order_id
FROM GLOBAL_SUPERSTORE.PUBLIC.GLOBALSUPERSTORE_ORDERS
LIMIT 10;
![Snowflake snippet 3](static/img/CookBook/Snowflake/IMG_003.png)