Data Manipulation to add left padded zeros

General

Data Manipulation to add left padded zeros

Hello Forum Users,

I am a new Teradata user and I'm struggling with data manipulation.  I have an existing table that is populated with ten digit customer id records and I need to create a new variable for these id records that is only 7 digits.  The existing ten digit id numbers vary in the number of zeros that pad the beginning of the number (ex. 0000001111, 0000011111, 0000111111, 0001111111, 0011111111, etc) until all of them are eventually non-zero digits.  In the event that the first three digits are non-zero integers, they are unimportant to keep and may be discarded from the new 7 digit number I'm trying to create.  Is there some combination of substring or data manipulation functions that could be used to yield a new 7 digit number that has leading zeros if there are fewer than 7 non-zero integers?

 

I was playing with an idea like the snippet below to add zeros but I don't know of a way to write a conditional clause that searches for the number of non-zero integers before determining how many zeros to put in the front of the new number.  Thank you in advance.  Your help is greatly appreciated.

SELECT 
/*CAST(NEW_CUST_ID AS CHAR(7)),*/
CUST_ID,CASE WHEN LENGTH(TRIM(CUST_ID))=10 THEN TRIM('000')||TRIM(CUST_ID) END AS COL2

FROM Database.Table
ORDER BY 1

 


Accepted Solutions
Highlighted
Teradata Employee

Re: Data Manipulation to add left padded zeros

SUBSTRING(CAST(CAST(CUST_ID AS FORMAT '9(10)') AS CHAR(10)) FROM 3 FOR 7) AS NEW_CUST_ID

1 ACCEPTED SOLUTION
3 REPLIES
Highlighted
Teradata Employee

Re: Data Manipulation to add left padded zeros

SUBSTRING(CAST(CAST(CUST_ID AS FORMAT '9(10)') AS CHAR(10)) FROM 3 FOR 7) AS NEW_CUST_ID

Re: Data Manipulation to add left padded zeros

 

I'm receiving a syntax error message: expected something between the ')' and the 'FROM' keyword.  I thought it was maybe an unmatched paretheses but that doesn't seem to be the problem.

 

SELECT 
CUST_ID,SUBSTRING(CAST(CAST(CUST_ID AS FORMAT'9(10)') AS CHAR(10) FROM 3 FOR 7) AS NEW_CUST_ID
FROM DATABASE.TABLE
ORDER BY 1

 

Junior Supporter

Re: Data Manipulation to add left padded zeros

Hi,

 

You missed out a ')' after CHAR(10) and hence the syntax error.

 

I am not sure of your requirement but according to my understanding you want your value of "0000011111" to become "0011111". Then a simple substring should work.

SELECT 
  CUST_ID
, SUBSTRING(CUST_ID FROM 4 FOR 7) AS NEW_CUST_ID
FROM 
  DATABASE.TABLE
ORDER BY 1;

 

Thanks,

Rohan Sawant