Azure SQL转Snowflake时FORMAT、STUFF、FOR XML PATH等效映射查询
Azure SQL迁移到Snowflake三类函数等效映射及示例
函数映射总览
- Azure SQL
FORMAT函数 → SnowflakeTO_CHAR函数,格式规则基本兼容,时区、数字/时间格式可通过locale参数对齐效果 - Azure SQL
STUFF + FOR XML PATH组合(行转列字符串拼接场景)→ SnowflakeLISTAGG函数,原生支持多行字符串拼接,无需额外处理前缀冗余字符
1. FORMAT函数示例对应实现
示例1:数字多格式输出
Azure SQL原逻辑为输出数字的普通数值格式、通用格式、货币格式,Snowflake对应实现如下:
SELECT CurrencyRateID, EndOfDayRate ,TO_CHAR(EndOfDayRate, '999G999G999D99', 'en-US') AS "Number Format" ,TO_CHAR(EndOfDayRate, 'G', 'en-US') AS "General Format" ,TO_CHAR(EndOfDayRate, 'L999G999G999D99', 'en-US') AS "Currency Format" FROM Sales.CurrencyRate ORDER BY CurrencyRateID LIMIT 5;
示例2:时间格式化输出
Azure SQL原逻辑为提取datetime类型的小时分钟,Snowflake对应实现如下:
SELECT TO_CHAR(CAST('2018-01-01 14:00' AS TIMESTAMP_NTZ), 'HH24:MI');
2. STUFF与FOR XML PATH组合示例对应实现
示例1:多行字符串拼接
Azure SQL原逻辑为将符合条件的多行字段拼接为逗号分隔的单字符串,Snowflake无需嵌套查询和冗余前缀处理,直接用LISTAGG实现:
SELECT LISTAGG(CONCAT(C_Key,',',A_Key,',',A_Number), ',') FROM db.Table a WHERE A_Number= '2134' AND IsLatest = 1;
内容的提问来源于stack exchange,提问作者Nikhil singh
相关产品推荐
相关产品推荐

