Skip to content

Instantly share code, notes, and snippets.

@goliatone
Created March 29, 2022 21:52
Show Gist options
  • Save goliatone/d5b2acf6d25ebf73854659466a03ad10 to your computer and use it in GitHub Desktop.
Save goliatone/d5b2acf6d25ebf73854659466a03ad10 to your computer and use it in GitHub Desktop.
[Snowflake base64 to UUID] #sql #snowflake Snowflake function to encode a base64 encoded UUID as a string formatted UUID
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==');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment