IBM DB2 LPAD/RPAD

Question

I use db2 v.9.1 on windows 2003 server so it can not use LPAD or RPAD functions scalar. because that functions support only z/OS right?

Now, I use this way for pad zero when COLUMN1 type is VARCHAR

 RIGHT('0000' ||  COLUMN1 ,4) AS  RPAD
 LEFT('0000' ||  COLUMN1 ,4) AS  LPAD

Have better way for replace LPAD or RPAD function?

Answer 1

REPEAT('0',4) || column_name

Now if you want to limit the 0 based on the number of characters you can use the RIGHT function and it would look something like this assuming your column is varchar(10):

RIGHT(REPEAT('0',4) || column_name, 10) in this case if you have characters it will fill it with 4 preceding 0s but if you have 7 characters it would fill it with 3 0s.

So you would have:
00001,
000012,
0000123,
00001234,
000012345,
0000123456,
0001234567,
0012345678,
etc.

Answer 2

I think you probably want the REPEAT scalar function.