06 Oct 2009
If it is already a character, and there is no sign, then just put the required number of zeroes on the front:
Select Substr('0000000000',1,10-Chars(Trim(col1))||
Trim(col1) (Char(10))
From TableX
;
If there is any possibility of a sign (presumably positive!). and you want to do it as a number:
Select Cast((Cast(Col1 As Integer) (Format '9(10)')) As Char(10) )
From TableX
;
Note that if the number is negative, you have just put it out as positive!
06 Oct 2009
jimm,
Thank you so very much for the solutions.
Both work fine in my case, since there is no sign involved.
You must sign in to leave a comment.
I am new to Teradata, so please ignore my lack of knowledge. Any help would be greatly appreciated.
I have char(10) field in a table, which holds a number. This number in most cases is 10 character long but not in all cases. So in case if the number is less than 10 characters(for example 000123456), I want to be able to convert it by adding a leading zero to it. I am doing something like following, but it is not converting correctly. I am doing the select to have the data exported in a file as fast export.
select cast(col1 as integer) (format '9(10)') from tablex
P.S. col1 is of type char
Please help.