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.
Not applicable

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.