This custom UDF converts numeric data to character data.
Custom UDF overloads
Parameters
FLOAT_EXPR: A numeric expression to be converted to varchar.
FORMAT: A varchar expression with the length and number of decimals of the resulting varchar. This format is automatically generated in SnowConvert.
CREATE OR REPLACEFUNCTIONPUBLIC.STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)RETURNSVARCHARLANGUAGESQLIMMUTABLECOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'AS$$TRIM(TRIM(SELECT TO_CHAR(FLOAT_EXPR, FORMAT)), '.')$$;CREATE OR REPLACEFUNCTIONPUBLIC.STR_UDF(FLOAT_EXPR FLOAT)RETURNSVARCHARLANGUAGESQLIMMUTABLECOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'AS$$ STR_UDF(FLOAT_EXPR, '999999999999999999')$$;
SQL Server
IN -> SqlServer_01.sql
SELECTSTR(123.5) as A,STR(123.5, 2) as B,STR(123.45, 6) as C,STR(123.45, 6, 1) as D;
Snowflake
OUT -> SqlServer_01.sql
SELECT PUBLIC.STR_UDF(123.5, '99999') as A, PUBLIC.STR_UDF(123.5, '99') as B, PUBLIC.STR_UDF(123.45, '999999') as C, PUBLIC.STR_UDF(123.45, '9999.9') as D;