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;

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}\-'),'-',''))
$$;
 

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;