After casting numeric numbers like varchar 0 digits disappear
To oversimplify the situation - an example that illustrates everything
select cast('0.0000' as numeric(26,4)) -- returns 0.0000 as should
select cast(cast('0.0000' as numeric(26,4)) as varchar (32)) -- returns .0000
but
select cast(cast('1.0001' as numeric(26,4)) as varchar (32)) -- returns 1.0001
How to store this 0 without instruction case
or is this the only way? And why is this happening?
source to share
The CASTs for VARCHAR are based on the FORMAT (which is similar to COBOL) of the column:
select cast('0.0000' as numeric(26,4));
'--(22).9(4)'
-> A smooth minus sign in front of the first desired digit (up to 22 digits) and four fractional digits, but you need at least one digit plus 4 fractional digits.
Either change this at the column level, or to this specific query:
select cast(cast('0.0000' as numeric(26,4) FORMAT '-(22)9.9(4)') as varchar (32));
0.0000
9
means a digit, for example. 9(10)
there will be 10 digits with leading zeros.
See Data Types and Formats in the Teradata manuals.
Btw if you know Oracle syntax and are using TD14 +, you can also use TO_CHAR, including most Oracle format options.
source to share