如何在Snowflake中创建等效于指定MSSQL逻辑的二进制解析函数

直接使用以下SQL创建自定义函数即可，完全对齐你提供的MSSQL逻辑：

CREATE OR REPLACE FUNCTION PARSE_BINARY_DATA(input BINARY)
RETURNS STRING
LANGUAGE SQL
AS $$
SELECT CASE
    WHEN SUBSTR(input, 1, 3) = TO_BINARY('00E700', 'HEX') 
        THEN TO_STRING(SUBSTR(input, 9, 510), 'UTF-16LE')
    WHEN SUBSTR(input, 1, 3) = TO_BINARY('00A700', 'HEX')
        THEN TO_STRING(SUBSTR(input, 9, 510), 'UTF8')
    WHEN SUBSTR(input, 1, 3) = TO_BINARY('01E700', 'HEX')
        THEN TO_STRING(SUBSTR(input, 9, TO_NUMBER(TO_VARCHAR(SUBSTR(input, 6, 1), 'HEX'), 'XX')), 'UTF-16LE')
    ELSE NULL
END
$$;

测试验证 #

你提供的示例测试值可以用以下语句验证结果：

SELECT PARSE_BINARY_DATA(TO_BINARY('00E70001010800084100470047004200', 'HEX')) AS parsed_result;

返回结果为AGGB，和MSSQL运行结果一致。

关键适配说明 #

和MSSQL语法的差异点主要有三处：

• Snowflake不支持0xXXX格式的二进制字面量，需要通过TO_BINARY('十六进制内容', 'HEX')做转换
• 二进制转字符串需要显式指定编码：MSSQL中CAST(二进制 AS NVARCHAR)默认对应UTF-16LE编码，CAST AS VARCHAR默认对应UTF8编码，Snowflake需要在TO_STRING函数中显式声明编码避免乱码
• 单字节二进制转整数需要先转十六进制字符串再转数值：MSSQL支持直接将单字节VarBinary强转为Int，Snowflake需要先转十六进制字符串，再通过TO_NUMBER指定十六进制格式转换为整数

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