Thanks Thanks Thanks Thanks Thanks

Right(Digits(your_number), 3) ....

Lenny

I used CAST(mycolumn as CHAR(3)) and it didn't work.

Thanks

What do you mean with "didn't work"?

select cast (1 as char(3))

result: 1, not 001

Well, it returns a string with 3 characters where the first 2 are blanks. So you have to replace those blacks with 0's.

So what's the syntax if using cast to do that?

Does this not work ?

Lenny

The CAST will left justify. e.g. cast(1 as char(3)) will give you '1 '
I am not sure what your exact question is.
Are you trying to have leading zeros for any int number or just for a 3 digit number?
Do you need to handle negative numbers?
Do you need to handle INT column only or decimal/smallint/ect. as well?
For instance:

1 => 001
10 => 010
100 => 100
1000 => ?
-1 => ?
1.0 => ?


If you ever only need up to 3 digits for INT, and do not have negative numbers, you can do:
SUBSTR( DIGITS(int_number), 8)
e.g. select substr(digits(1), 8) from sysibm.sysdummy1 => '001'


--Andrei L.

No, Right(Digits(your_number), 3)
is good for me.

I only have INT and no negative. I thought Cast(1 as char(3)) will give me 003, now I know it is not.

Thanks,

Sounds good for me.

Lenny