Pad the leading zeros

General
Enthusiast

Pad the leading zeros

Hello Friends,

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.

 

Update T1

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.

9 REPLIES
Enthusiast

Re: Pad the leading zeros

Use "Substr" instead of "Substring". "Substr" is the Teradata database function for substring. "Substring" is the ODBC function for substring. They work differently.

Enthusiast

Re: Pad the leading zeros

I tried it but the data is not updated.

Enthusiast

Re: Pad the leading zeros

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

Enthusiast

Re: Pad the leading zeros

Thank you. It worked.

Junior Contributor

Re: Pad the leading zeros

There's no need for SUBSTRING/CHAR_LENGTH, better use LPAD:

 

Set X = LPAD(T1.X,4,'0')

 

Enthusiast

Re: Pad the leading zeros

The first time I used LPAD. But the data didnt get updated.

Junior Contributor

Re: Pad the leading zeros

What you mean by "didnt get updated"?
The value was the same as before, didn't change??
Enthusiast

Re: Pad the leading zeros

Yes, the values are same as before. They didn't change.
Junior Contributor

Re: Pad the leading zeros

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')