[Snowflake base64 to UUID] #sql #snowflake Snowflake function to encode a base64 encoded UUID as a string formatted UUID

base64_to_uuid.sql

DROP FUNCTION _BASE64_UUID(VARCHAR);
CREATE OR REPLACE FUNCTION _BASE64_UUID(INPUT VARCHAR)
    RETURNS varchar
LANGUAGE javascript
strict
AS
$$
    try {
        var stmt = snowflake.createStatement({ 
            sqlText: `SELECT TO_VARCHAR(TRY_BASE64_DECODE_BINARY(?), 'HEX');`,
            binds: [INPUT]
        });
        var hex = stmt.execute().next().getColumnValue(1);
        stmt = snowflake.createStatement({ 
            sqlText: `
            SELECT
                CONCAT(
                    SUBSTRING(:1::varchar, 1, 8), '-',
                    SUBSTRING(:2::varchar, 9, 4), '-', 
                    SUBSTRING(:3::varchar, 13, 4), '-', 
                    SUBSTRING(:4::varchar, 17, 4), '-', 
                    SUBSTRING(:5::varchar, 21, 12)
            );`,
            binds: [hex, hex, hex, hex, hex]
        });
        return stmt.execute().next().getColumnValue(1);
    } catch (err)  {
        return "Failed stmt 1: " + err;
    }
$$;

SELECT _BASE64_UUID('prn8FJPsRWCPRsNTpZDj0Q==');