Pad the leading zeros

Database

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 Result
CLNT_I 0000000123
CLNT_I=10(without sign) 1234567890 1234567890
CLNT_I 0000000123
CLNT_I=10 (with sign) -1234567890 - 1234567890

Please let me know the function.

Thanks,
Manj
3 REPLIES
Teradata Employee

Re: Pad the leading zeros

CAST(CAST(CLNT_I AS FORMAT'-9(10)') AS CHAR(11))

Re: Pad the leading zeros

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

Re: Pad the leading zeros

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).