All Forums Database
hmanjunath4u 1 post Joined 05/09
21 May 2009
Pad the leading zeros

Hi I have the requirement in my project, i need to pad the leading zeros to the account number. to make the account number length equal to 10+ one space at first place.Condition Source Value Source Value ResultCLNT_I 0000000123CLNT_I=10(without sign) 1234567890 1234567890CLNT_I 0000000123CLNT_I=10 (with sign) -1234567890 - 1234567890Please let me know the function.Thanks,Manj

Fred 1096 posts Joined 08/04
21 May 2009


Sabrina 1 post Joined 10/09
03 Oct 2009

I have two fields that I need to pad leading zeros. One requires 12 digits with pad leading zeros, such as 001123456789. The other requires 10 digits with pad leading zeros, but last two are decimals, such as 000001000. Here is my line of code. I can't tweak the code in the first line to work for 12-digit. when I changed from '9(10)' to '12(13)' I got invalid error message. The second line of code did not return pad leading zeros. What is wrong here.CAST(CAST(acct_id '9(10)') AS CHAR(12)) ,amt_due (decimal(11,2), format '$$$,$$$,$$9.99')Thanks for any helps!Sabrina

bfabros 1 post Joined 08/11
13 Feb 2012

I am new to Teradata and I had to convert a 11-13 digit decimal to a char(13) with leading zeros.  Thanks Fred, this worked for me:

CAST(CAST(<In_Field> AS FORMAT'9(13)') AS CHAR(13)) as <New_Char13>

Example: 55,123,456,789 (In_Field)  Result: 0055123456789 (New_Char13).


You must sign in to leave a comment.