I have a table A with column x and has 3 characters. Now I need to update the column by adding a leading zero. The total length of column is 4. I am trying to update as below but its not working. Please let me know where i am going wrong.
from databasename.A T1
Set X = Substring('0000',1,4-Length(T1.X)) || T1.X
The error is 3706 expected something between a string or a unicode character literal and ','
Thanks in advance.
Use "Substr" instead of "Substring". "Substr" is the Teradata database function for substring. "Substring" is the ODBC function for substring. They work differently.
If X is defined as CHAR(4), it will be padded with spaces, so the length will still be 4. Try this:
Set X = Substr('0000',1,4-Length(Trim(Trailing ' ' From T1.X))) || T1.X
If the column is defined as a fixed CHAR(4) there are leading blanks and you need to apply a TRIM first:
Set X = LPAD(TRIM(T1.X),4,'0')