Snowflake Function Creation

Question

I am trying to implement the below functionality in Snowflake by creating a function

Declare
@test VarBinary(518)=0x00E70001010800084100470047004200 
Select Case 
When SubString(@test, 1, 3) = 0x00E700 Then CAST(SubString(@test, 9, 510) As NVarchar(255)) 
When SubString(@test, 1, 3) = 0x00A700 Then CAST(SubString(@test, 9, 510) As Varchar(510)) 
When SubString(@test, 1, 3) = 0x01E700 Then CAST(SubString(@test, 9, CAST(SubString(@test, 6, 1) As Int)) As NVarchar(64)) 
Else Null 
End 

The above code is working in mssql but I am trying unable to create a function in Snowflake.

How can I create the function in Snowflake?

Answer 1

The following example might help:

CREATE OR REPLACE FUNCTION MMfGetPropValChar(p1 varbinary)
RETURNS varchar
as
$$
  select 
    CASE
      WHEN substr(p1::varchar, 1, 3) = '0x00E700' 
         then substr(p1::varchar, 9, 510)::varchar
      WHEN substr(p1::varchar, 1, 3) = '0x01E700' 
         then substr(p1::varchar, 9, substr(p1::varchar, 6, 1))::varchar
   ELSE current_timestamp()::varchar
   End as test
$$
;


SELECT MMfGetPropValChar(to_binary(hex_encode('Snowflake'), 'HEX')) as res;

RES
2021-10-05 05:14:52.369 -0700

Note: I cast to varchars using the :: notation, just to make the SQL a little easier to read.