Dynamic case when statement for adding leading zeroes

General

Dynamic case when statement for adding leading zeroes

Hello Teradata Community, 

 

I have a CASE WHEN statement that is intended to add leading zeros to the front of an account number to arrive at exactly 23 digits in CHAR(23) format.  But some of the account numbers in the population are 16 digits and others are only 10 digits.  Is there a way to dynamically write a CASE WHEN statement such that even when the account numbers vary in length, you will still get exactly 23 digits with the number of leading zeroes that precede the account number being the only thing that changes?  The following code below works for account numbers that are 16 digits but it fails if the number length is 10 digits.

 

I searched throughout the forum and had trouble finding this exact scenario.  Thank you very much in advance for your help - it is greatly appreciated.  

 

This is the CASE WHEN statement that I'm using: 

SELECT

    T1.*,

    CASE WHEN(T1.prod_cd = 'ABC' THEN CAST('0000000'||TRIM(T1.acct_nr) AS CHAR(23))

        ELSE T1.acct_nr END AS New_Acct_Nr

 

~ RM

3 REPLIES
Teradata Employee

Re: Dynamic case when statement for adding leading zeroes

If acct_nr is BIGINT or DECIMAL, just apply a FORMAT:  CAST(CAST(acct_nr AS FORMAT '9(23)') as CHAR(23))

 

If it's already a character string, LPAD(acct_nr,23,'0')

Junior Supporter

Re: Dynamic case when statement for adding leading zeroes

RM,

 

Something simpler:  SUBSTR('00000000000000',1,23-LENGTH(TRIM(ACCT_INPUT))) || TRIM(ACCT_INPUT)

 

Cheers,

Teradata Frank, Certified Master

Re: Dynamic case when statement for adding leading zeroes

Thanks Fred!