LPAD Function equivalent in Teradata

Database
Enthusiast

LPAD Function equivalent in Teradata

Friends,
I am trying to find something equivalent to LPAD in Teradata. LPAD will allow me to add two leading zeros in front of my sequence number. This is what I am trying to do,

1. I have a field SEQ_NO, which has three type of values - 01, 0001, 1009. Which I have a 01, I want it to be converted to 0001 - add two zeros on the left.

I have not found a function or utility in TD to do this.

Any help would be appreciated.
7 REPLIES
Enthusiast

Re: LPAD Function equivalent in Teradata

select SEQ_NO (format '9999') ... ;
Enthusiast

Re: LPAD Function equivalent in Teradata

If your SEQ_NO, is a CHAR column, then you can try.

SELECT SEQ_NO (INTEGER FORMAT '9999')

.

Re: LPAD Function equivalent in Teradata

This shows correctly in Bteq but may not display correctly in SQL assistant
Enthusiast

Re: LPAD Function equivalent in Teradata

SQL assistant uses Microsoft to format its datatypes.
Assuming your seq-no is already a character datatype:

Select Substring('0000' From 1 For Chars(Trim(Seq_No)))||Trim(Seq_No)
From tbl;

(This will also work for numeric seq_no).
Junior Supporter

Re: LPAD Function equivalent in Teradata

You may find this useful:

http://carlosal.wordpress.com/2009/04/07/lpad-en-teradata/

(in spanish, as usual)

HTH.

Cheers.

Carlos.

Re: LPAD Function equivalent in Teradata

Hi Jimm,

Select Substring('0000' From 1 For Chars(Trim(Seq_No)))||Trim(Seq_No)From tbl;
is similar to
Select Substring('0000' , 1 ,length(Seq_No)))||Trim(Seq_No) From tbl;

1001 = 00001001
333 = 000333
1 = 01
1003 = 00001003

This is not equivalent to LPAD
Enthusiast

Re: LPAD Function equivalent in Teradata

Apologies; copied over the wrong query!

Select Substring('0000' From 1 For 4-Chars(Trim(Seq_No)))||Trim(Seq_No)
From Tbl
Order By 1;

Works fine.