All Forums UDA
ChelleV 5 posts Joined 05/07
02 May 2007
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!

joedsilva 505 posts Joined 07/05
03 May 2007

Play around with the FORMAT clause.SELECT DecimalCol (FORMAT 'Z9') (CHAR(7)) FROM MyTable;

ChelleV 5 posts Joined 05/07
03 May 2007

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!

joedsilva 505 posts Joined 07/05
04 May 2007

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.

ChelleV 5 posts Joined 05/07
04 May 2007

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!

ashishc 1 post Joined 07/06
05 May 2007

this worked for me ...cast ( (cast(FieldName) as INTEGER) as CHAR(7) )-- Ashish

You must sign in to leave a comment.