STR UDF

Description

This custom UDF converts numeric data to character data.

Custom UDF overloads

Parameters

1. FLOAT_EXPR: A numeric expression to be converted to varchar.

2. FORMAT: A varchar expression with the length and number of decimals of the resulting varchar. This format is automatically generated in SnowConvert.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE 
AS
$$
    TRIM(TRIM(SELECT TO_CHAR(FLOAT_EXPR, FORMAT)), '.')
$$;

CREATE OR REPLACE FUNCTION PUBLIC.STR_UDF(FLOAT_EXPR FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE 
AS
$$
    STR_UDF(FLOAT_EXPR, '999999999999999999')
$$;

SQL Server

Query

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;

Result

A	B	C	D
124	**	123	123.5

Snowflake

Query

SELECT
    STR_UDF(123.5, '99999') as A,
    STR_UDF(123.5, '99') as B,
    STR_UDF(123.45, '999999') as C,
    STR_UDF(123.45, '9999.9') as D;

Result

A	B	C	D
124	##	123	123.5

Related EWIs

1. MSCEWI1020: CUSTOM UDF INSERTED.