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:
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
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')
Something simpler: SUBSTR('00000000000000',1,23-LENGTH(TRIM(ACCT_INPUT))) || TRIM(ACCT_INPUT)