All Forums Database
kuldeepsingh 6 posts Joined 08/06
02 Mar 2007
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.

Jim Chapman 449 posts Joined 09/04
02 Mar 2007

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

joedsilva 505 posts Joined 07/05
03 Mar 2007

If your SEQ_NO, is a CHAR column, then you can try.SELECT SEQ_NO (INTEGER FORMAT '9999').

2visakh 4 posts Joined 10/10
01 Nov 2010

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

Jimm 298 posts Joined 09/07
01 Nov 2010

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

CarlosAL 512 posts Joined 04/08
02 Nov 2010

You may find this useful:

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

(in spanish, as usual)

HTH.

Cheers.

Carlos.

2visakh 4 posts Joined 10/10
02 Nov 2010

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

Jimm 298 posts Joined 09/07
02 Nov 2010

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.

You must sign in to leave a comment.