SQL转Snowflake兼容版本报错：is_Double函数使用问题排查

问题背景 #

将SQL Server风格的查询转换为Snowflake兼容语句后，执行时出现编译错误，错误定位在判断字符串是否为数值的is_Double函数调用处。

原始SQL（SQL Server风格） #

SELECT 
   LEFT(Replace([SERIAL_NBR],'"',''),34) AS [SERIAL_NBR]
   ,CONVERT(datetime, Replace([INSERTED_DTM],'"',''), 103) AS [INSERTED_DTM]
   ,LEFT(Replace([GROUP_NAME],'"',''),1024)
   ,LEFT(Replace([FIRST_NAME],'"',''),50)
   ,LEFT(Replace([LAST_NAME],'"',''),50)
   ,LEFT(Replace([REASON_CODE_ID],'"',''),30)
   ,CASE isnumeric(Replace([VALUE],'"',''))
       when 1 
            then CAST(Replace([VALUE],'"','') AS float)
            else null
     END AS [VALUE]
    ,LEFT(Replace([AUTOLOAD_DELIVERY_STATE_ID],'"',''),15)
    ,LEFT(Replace([RIDER_CLASS],'"',''), 20)
    ,LEFT(Replace([ADJUSTMENT_NOTES],'"',''), 1024)
FROM X 

修改后报错的Snowflake SQL #

SELECT 
    LEFT(Replace(SERIAL_NBR,'"',''),34) AS SERIAL_NBR
    ,to_timestamp(Replace(INSERTED_DTM,'"',''), 'DD/MM/YYYY') AS INSERTED_DTM
    ,LEFT(Replace(GROUP_NAME,'"',''),1024)
    ,LEFT(Replace(FIRST_NAME,'"',''),50)
    ,LEFT(Replace(LAST_NAME,'"',''),50)
    ,LEFT(Replace(REASON_CODE_ID,'"',''),30)
    ,CASE 
       WHEN is_Double(Replace(VALUE,'"','')) = 1 
       THEN CAST(Replace(VALUE,'"','') AS NUMBER)
       ELSE NULL
     END AS VALUE
    ,LEFT(Replace(AUTOLOAD_DELIVERY_STATE_ID,'"',''),15)
    ,LEFT(Replace(RIDER_CLASS,'"',''), 20)
    ,LEFT(Replace(ADJUSTMENT_NOTES,'"',''), 1024)
FROM stage."TL_A2_Adjustment_Note"
WHERE Replace(SERIAL_NBR,'"','') != 'A2'

错误信息 #

001044 (42P13): SQL compilation error: error line 9 at position 9

错误原因与修正方案 #

Snowflake中不存在is_Double函数，要替代SQL Server的ISNUMERIC逻辑，需使用Snowflake原生的TRY_TO_NUMBER或TRY_CAST函数：

• TRY_TO_NUMBER(字符串)：尝试将字符串转为数值，成功则返回对应数值，失败返回NULL，无需额外判断等于1
• TRY_CAST(字符串 AS NUMBER)：和TRY_TO_NUMBER效果一致

另外，重复调用Replace(VALUE,'"','')可以通过子查询或CROSS APPLY提取，提升代码可读性，不过并非必须。

修正后的完整SQL #

简化版（直接用TRY_TO_NUMBER） #

SELECT 
    LEFT(Replace(SERIAL_NBR,'"',''),34) AS SERIAL_NBR
    ,TO_TIMESTAMP(Replace(INSERTED_DTM,'"',''), 'DD/MM/YYYY') AS INSERTED_DTM
    ,LEFT(Replace(GROUP_NAME,'"',''),1024)
    ,LEFT(Replace(FIRST_NAME,'"',''),50)
    ,LEFT(Replace(LAST_NAME,'"',''),50)
    ,LEFT(Replace(REASON_CODE_ID,'"',''),30)
    -- 用TRY_TO_NUMBER替代不存在的is_Double
    ,TRY_TO_NUMBER(Replace(VALUE,'"','')) AS VALUE
    ,LEFT(Replace(AUTOLOAD_DELIVERY_STATE_ID,'"',''),15)
    ,LEFT(Replace(RIDER_CLASS,'"',''), 20)
    ,LEFT(Replace(ADJUSTMENT_NOTES,'"',''), 1024)
FROM stage."TL_A2_Adjustment_Note"
WHERE Replace(SERIAL_NBR,'"','') != 'A2'

保留CASE结构版（贴近原始逻辑） #

SELECT 
    LEFT(Replace(SERIAL_NBR,'"',''),34) AS SERIAL_NBR
    ,TO_TIMESTAMP(Replace(INSERTED_DTM,'"',''), 'DD/MM/YYYY') AS INSERTED_DTM
    ,LEFT(Replace(GROUP_NAME,'"',''),1024)
    ,LEFT(Replace(FIRST_NAME,'"',''),50)
    ,LEFT(Replace(LAST_NAME,'"',''),50)
    ,LEFT(Replace(REASON_CODE_ID,'"',''),30)
    ,CASE 
       WHEN TRY_TO_NUMBER(Replace(VALUE,'"','')) IS NOT NULL
       THEN CAST(Replace(VALUE,'"','') AS NUMBER)
       ELSE NULL
     END AS VALUE
    ,LEFT(Replace(AUTOLOAD_DELIVERY_STATE_ID,'"',''),15)
    ,LEFT(Replace(RIDER_CLASS,'"',''), 20)
    ,LEFT(Replace(ADJUSTMENT_NOTES,'"',''),1024)
FROM stage."TL_A2_Adjustment_Note"
WHERE Replace(SERIAL_NBR,'"','') != 'A2'

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