bigquery_base64_to_hex.sql

#standardSQL
CREATE TEMPORARY FUNCTION
    BASE64_TO_HEX(code BYTES)
    RETURNS STRING
    LANGUAGE js AS """
        var alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
        var equals = 0;
        if (!code || code.length == 0) {
            return null;
        }
        while (code[code.length - 1] == "=") {
            equals++;
            code = code.slice(0, -1);
        }
        var binaryRep = "";
        var hexRep = "";
        var width = 6;
        for (var i = 0; i < code.length; i++) {
            var index = alphabet.indexOf(code.charAt(i));
            var n = (index >>> 0).toString(2);
            var bin = n.length >= width ? n : new Array(width - n.length + 1).join('0') + n;
            binaryRep += bin;
        }
        for (var i = 0; i < equals; i++) {
            binaryRep = binaryRep.slice(0, -2);
        }
        for (var i = 0; i < binaryRep.length / 4; i++) {
            hexRep += parseInt(binaryRep.charAt(4 * i) + binaryRep.charAt(4 * i + 1) + binaryRep.charAt(4 * i + 2) + binaryRep.charAt(4 * i + 3), 2).toString(16);
        }
        return hexRep;
    """
;
SELECT BASE64_TO_HEX(SHA1("test_string"))
;