如何在Snowflake中实现MSSQL对Varbinary的截取转Float等效功能

核心逻辑对齐 #

MSSQL处理二进制类型的SUBSTRING规则为：自动排除开头0x前缀，索引参数每1单位对应1字节（即2个十六进制字符），Snowflake原生字符串SUBSTRING按单个字符计数，所以我们先做逻辑对齐再实现对应功能。

步骤1：实现模拟MSSQL二进制截取的通用函数 #

CREATE OR REPLACE FUNCTION MSSQL_BINARY_SUBSTRING(p_hex_str VARCHAR, p_start_byte INT, p_length_byte INT)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
-- 去掉0x前缀后按字节位置截取，再补回0x前缀
SELECT '0x' || SUBSTR(SUBSTR(p_hex_str, 3), (p_start_byte - 1) * 2 + 1, p_length_byte * 2)
$$;

步骤2：执行和MSSQL输出完全对齐的查询 #

WITH input AS (SELECT '0x003800010102000500000000' AS hex_str)
SELECT
  MSSQL_BINARY_SUBSTRING(hex_str, 1, 2) AS A,
  MSSQL_BINARY_SUBSTRING(hex_str, 6, 1) AS B,
  -- 先取B的数值作为截取长度，再截取对应字节转float
  TO_NUMBER(
    SUBSTR(MSSQL_BINARY_SUBSTRING(hex_str, 9, TO_NUMBER(SUBSTR(MSSQL_BINARY_SUBSTRING(hex_str,6,1),3), 'XX')), 3)
  )::FLOAT AS D
FROM input;

执行结果和你提供的MSSQL输出完全一致：

步骤3：封装为你需要的getFloat1函数 #

如果你不需要通用截取函数，可以直接把逻辑内嵌到目标函数中：

CREATE OR REPLACE FUNCTION getFloat1(p1 VARCHAR)
RETURNS FLOAT
LANGUAGE SQL
AS $$
SELECT TO_NUMBER(
    SUBSTR(p1, 3 + (9-1)*2 + 1, TO_NUMBER(SUBSTR(p1, 3 + (6-1)*2 + 1, 2), 'XX') * 2)
)::FLOAT
$$;

注意事项 #

如果你的输入是BINARY类型而非带0x前缀的VARCHAR类型，入参先做转换即可：TO_VARCHAR(你的二进制字段, 'HEX')。

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