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 REPLACE FUNCTION PUBLIC.STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
TRIM(TRIM(SELECT TO_CHAR(FLOAT_EXPR, FORMAT)), '.')
$$;
CREATE OR REPLACE FUNCTION PUBLIC.STR_UDF(FLOAT_EXPR FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
STR_UDF(FLOAT_EXPR, '999999999999999999')
$$;
Transact-SQL
IN -> SqlServer_01.sql
SELECT
STR(123.5) as A,
STR(123.5, 2) as B,
STR(123.45, 6) as C,
STR(123.45, 6, 1) as D;
A
B
C
D
124
**
123
123.5
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;
