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
Solved! Go to Solution.
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
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;