format char fields to have leading zeroes

Database

format char fields to have leading zeroes

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.
3 REPLIES
Enthusiast

Re: format char fields to have leading zeroes

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!

Re: format char fields to have leading zeroes

jimm,

Thank you so very much for the solutions.
Both work fine in my case, since there is no sign involved.

Re: format char fields to have leading zeroes

Hi, I have similar question.. I need to align a string in center in a file using bteq export. How we can do this in Teradata. Please help me.
Thanks in advance.