Converting Decimal to CHAR

UDA
Enthusiast

Converting Decimal to CHAR

I am fairly new to teradata; I need to convert a DECIMAL 2,0 field to a CHAR(7), as well as trim the trailing '.'.

I've tried:

trim(trailing '.' from
(CAST((FieldName)
AS CHAR(7))))

and some substr(... variations, but I continue to get the '.' at the end.

Thank you!
5 REPLIES
Enthusiast

Re: Converting Decimal to CHAR

Play around with the FORMAT clause.

SELECT DecimalCol (FORMAT 'Z9') (CHAR(7)) FROM MyTable;
Enthusiast

Re: Converting Decimal to CHAR

Thank you, this worked perfectly for what I needed. There is only one digit coming from the decial column to the CHAR(7) field, so I used DecCol (FORMAT 'Z')(CHAR(7)).

Thank you very much!
Enthusiast

Re: Converting Decimal to CHAR

if you use 'Z' in the format it will return space for any value of 0 in the column, so you will have to use '9' instead of 'Z' if you want it to be zero instead of a space.
Enthusiast

Re: Converting Decimal to CHAR

Thank you, I didn't know this! I actually do want the zeros to be spaces for this instance, but I have another that needs to show the 0.

Have a great day!

Re: Converting Decimal to CHAR

this worked for me ...

cast ( (cast(FieldName) as INTEGER) as CHAR(7) )

-- Ashish