Cast from Number Datatypes to Varchar Datatype

Translation specification for the transformation of cast Varchar Datatype Snowflake.

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

(('"'||12)||'"')|
----------------+
"12"            |

Snowflake

Query

OUT -> Teradata_01.sql

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

Result

"'""'|| LEFT(TO_VARCHAR(CAST(12 AS BYTEINT), 'TM'), 10) ||'""'"
---------------------------------------------------------------
"12"            

SMALLINT

Teradata

Query

IN -> Teradata_02.sql

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

Result

(('"'||123)||'"')|
-----------------+
"123"            |

Snowflake

Query

OUT -> Teradata_02.sql

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

Result

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

INTEGER

Teradata

Query

IN -> Teradata_03.sql

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

Result

(('"'||12345)||'"')|
-------------------+
"12345"            |

Snowflake

Query

OUT -> Teradata_03.sql

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

Result

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

BIGINT

Teradata

Query

IN -> Teradata_04.sql

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

Result

(('"'||12345)||'"')|
-------------------+
"12345"            |

Snowflake

Query

OUT -> Teradata_04.sql

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

Result

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

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

(('"'||12345)||'"')|(('"'||12345)||'"')|
-------------------+-------------------+
"12345."           |"12345.00"         |

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

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

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

COLUMN1          |COLUMN2
-----------------+--------------+
"-.10"           |".10"         |

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

COLUMN1           |COLUMN2
------------------+---------------+
"-0.10"           |"0.10"         |

Related EWIs

No related EWIs.