Cast from Number Datatypes to Varchar Datatype

Translation specification for the transformation of cast Varchar Datatype Snowflake.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Teradata when casts to varchar uses default formats for each number datatype, so Snowconvert adds formats to keep the equivalence among platforms.

Sample Source Patterns

BYTEINT

Teradata

Query

IN -> Teradata_01.sql

SELECT '"'||cast(cast(12 as BYTEINT) as varchar(10))||'"';

Result

Snowflake

Query

OUT -> Teradata_01.sql

SELECT
'"'|| LEFT(TO_VARCHAR(cast(12 as BYTEINT), 'TM'), 10) ||'"';

Result

SMALLINT

Teradata

Query

IN -> Teradata_02.sql

SELECT '"'||cast(cast(123 as SMALLINT) as varchar(10))||'"';

Result

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
'"'|| LEFT(TO_VARCHAR(CAST(123 AS SMALLINT), 'TM'), 10) ||'"';

Result

INTEGER

Teradata

Query

IN -> Teradata_03.sql

SELECT '"'||cast(cast(12345 as INTEGER) as varchar(10))||'"';

Result

Snowflake

Query

OUT -> Teradata_03.sql

SELECT
'"'|| LEFT(TO_VARCHAR(CAST(12345 AS INTEGER), 'TM'), 10) ||'"';

Result

BIGINT

Teradata

Query

IN -> Teradata_04.sql

SELECT '"'||cast(cast(12345 as BIGINT) as varchar(10))||'"';

Result

Snowflake

Query

OUT -> Teradata_04.sql

SELECT
       '"'|| LEFT(TO_VARCHAR(CAST(12345 AS BIGINT), 'TM'), 10) ||'"';

Result

DECIMAL[(n[,m])] or NUMERIC[(n[,m])]

Teradata

Query

IN -> Teradata_05.sql

SELECT '"'||cast(cast(12345 as DECIMAL) as varchar(10))||'"',
       '"'||cast(cast(12345 as DECIMAL(12, 2)) as varchar(10))||'"';

Result

Snowflake

Query

OUT -> Teradata_05.sql

SELECT
'"'|| LEFT(TO_VARCHAR(CAST(12345 AS DECIMAL), 'TM.'), 10) ||'"',
'"'|| LEFT(TO_VARCHAR(CAST(12345 AS DECIMAL(12, 2)), 'TM'), 10) ||'"';

Result

Known Issues

• Teradata treats the numbers between 0 and 1 differently than Snowflake. For those values, Teradata does not add the zero before the dot; meanwhile, Snowflake does.

Teradata

Query

IN -> Teradata_06.sql

SELECT '"'||cast(cast(-0.1 as DECIMAL(12, 2)) as varchar(10))||'"' AS column1,
       '"'||cast(cast(0.1 as DECIMAL(12, 2)) as varchar(10))||'"' AS column2;

Result

Snowflake

Query

OUT -> Teradata_06.sql

SELECT
'"'|| LEFT(TO_VARCHAR(CAST(-0.1 AS DECIMAL(12, 2)), 'TM'), 10) ||'"' AS column1,
'"'|| LEFT(TO_VARCHAR(CAST(0.1 AS DECIMAL(12, 2)), 'TM'), 10) ||'"' AS column2;

Result

Related EWIs

No related EWIs.