请求实现Snowflake中hex转base2二进制的自定义UDF函数

Snowflake暂无内置函数直接将十六进制字符串转换为完整的二进制字符串，以下提供两种自定义UDF实现方式，完全支持你通过SELECT HEXTOBINARY(column_a)的方式调用。

方法一：JavaScript UDF（简洁高效） #

利用JavaScript的字符串处理能力，快速实现转换，自动为每个十六进制字符补全4位二进制前导零：

CREATE OR REPLACE FUNCTION HEXTOBINARY(hex_str VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    if (!HEX_STR) return null;
    let binaryStr = '';
    for (let char of HEX_STR) {
        // 将单个十六进制字符转为4位二进制，补前导零
        const nibble = parseInt(char, 16).toString(2).padStart(4, '0');
        binaryStr += nibble;
    }
    return binaryStr;
$$;

方法二：纯SQL UDF（无依赖） #

如果偏好纯SQL实现，无需依赖JavaScript引擎，可通过递归CTE逐个处理十六进制字符：

CREATE OR REPLACE FUNCTION HEXTOBINARY(hex_str VARCHAR)
RETURNS VARCHAR
AS $$
WITH RECURSIVE hex_processing AS (
    SELECT 
        1 AS pos,
        SUBSTR(hex_str, 1, 1) AS curr_char,
        CASE SUBSTR(hex_str, 1, 1)
            WHEN '0' THEN '0000' WHEN '1' THEN '0001' WHEN '2' THEN '0010' WHEN '3' THEN '0011'
            WHEN '4' THEN '0100' WHEN '5' THEN '0101' WHEN '6' THEN '0110' WHEN '7' THEN '0111'
            WHEN '8' THEN '1000' WHEN '9' THEN '1001' WHEN 'A' THEN '1010' WHEN 'B' THEN '1011'
            WHEN 'C' THEN '1100' WHEN 'D' THEN '1101' WHEN 'E' THEN '1110' WHEN 'F' THEN '1111'
            WHEN 'a' THEN '1010' WHEN 'b' THEN '1011' WHEN 'c' THEN '1100' WHEN 'd' THEN '1101'
            WHEN 'e' THEN '1110' WHEN 'f' THEN '1111' ELSE ''
        END AS curr_binary
    WHERE hex_str IS NOT NULL AND LENGTH(hex_str) > 0
    UNION ALL
    SELECT
        pos + 1,
        SUBSTR(hex_str, pos + 1, 1),
        CASE SUBSTR(hex_str, pos + 1, 1)
            WHEN '0' THEN '0000' WHEN '1' THEN '0001' WHEN '2' THEN '0010' WHEN '3' THEN '0011'
            WHEN '4' THEN '0100' WHEN '5' THEN '0101' WHEN '6' THEN '0110' WHEN '7' THEN '0111'
            WHEN '8' THEN '1000' WHEN '9' THEN '1001' WHEN 'A' THEN '1010' WHEN 'B' THEN '1011'
            WHEN 'C' THEN '1100' WHEN 'D' THEN '1101' WHEN 'E' THEN '1110' WHEN 'F' THEN '1111'
            WHEN 'a' THEN '1010' WHEN 'b' THEN '1011' WHEN 'c' THEN '1100' WHEN 'd' THEN '1101'
            WHEN 'e' THEN '1110' WHEN 'f' THEN '1111' ELSE ''
        END
    FROM hex_processing
    WHERE pos < LENGTH(hex_str)
)
SELECT LISTAGG(curr_binary, '') WITHIN GROUP (ORDER BY pos)
FROM hex_processing
$$;

测试验证 #

执行以下语句测试转换效果：

SELECT HEXTOBINARY('2303067e') AS binary_result;

返回结果：

00100011000000110000011001111110

注意事项 #

• 两种UDF均支持大小写十六进制字符（如2303067E和2303067e都能正确转换）
• 若输入为空值，JS版本返回null，SQL版本返回空字符串；若输入含非法字符，JS版本会抛出错误，SQL版本会忽略非法字符继续转换
• 调用方式完全符合需求：SELECT HEXTOBINARY(column_a) FROM your_table;

内容的提问来源于stack exchange，提问作者2023hpy