Padding 10 leading zeros on a Case.

Database

Padding 10 leading zeros on a Case.

I'm struggling to find a solution to pad leading zeros on the following case statement.  RSSI.Supply_Chain_Item_Num/Prev can be 5 to 8 digits in length the field i'm joining on is 10.  I know it's a Substring i need, but what?

 

CASE WHEN RSSI.Supply_Chain_Item_Num_Prev IS NULL OR RSSI.Supply_Chain_Item_Num_Prev = ' ' THEN RSSI.Supply_Chain_Item_Num
WHEN RSSI.Supply_Chain_Item_Num_Prev = RSSI.Supply_Chain_Item_Num THEN RSSI.Supply_Chain_Item_Num
WHEN RSSI.Supply_Chain_Item_Num_Prev <> RSSI.Supply_Chain_Item_Num AND RSSI.Supply_Chain_Item_Num_Prev < RSSI.Supply_Chain_Item_Num THEN RSSI.Supply_Chain_Item_Num
WHEN RSSI.Supply_Chain_Item_Num_Prev <> RSSI.Supply_Chain_Item_Num AND RSSI.Supply_Chain_Item_Num < RSSI.Supply_Chain_Item_Num_Prev THEN RSSI.Supply_Chain_Item_Num_Prev
END AS DerrSupplyChainItemNum


Accepted Solutions
Senior Apprentice

Re: Padding 10 leading zeros on a Case.

Either LPAD(DerrSupplyChainItemNum, 10, '0')  or TRIM((DerrSupplyChainItemNum (format '9(10)')))

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Padding 10 leading zeros on a Case.

Try the LPAD function, something like

 

LPAD(column,'0',num)

Senior Apprentice

Re: Padding 10 leading zeros on a Case.

Either LPAD(DerrSupplyChainItemNum, 10, '0')  or TRIM((DerrSupplyChainItemNum (format '9(10)')))