format an Decimal output with leading zeros URGENT 1

[DFW1999]

Hello,

I need to format output for a decimal value with the leading zeros(total of 6 character long).

Here is the info regarding the column

column_Name Col_A
Col datatype Decimal FORMAT '----.9999

Here is my query:

select G.Col_A
from TABLE_G G
where G.Col_A IS NOT NULL
AND G.Col_A <>''
sample 10

SCENARIO:

the max value for this col is 500.000 and Min value is 1.000.

I want the out put for Max as : 000500
and for MIN : 000001

Fixed length.

Please this is urgent. I always appreciate your help.

thanks

 

[DFW1999]

Hello All,

I got the following solution:

sel 123 (format'999999') (CHAR(6)) as ch6;


Thanks

 

[rrrkrishnan]

There might be a better way to do this, but this works as well.

select substr('00000',1,5-characters(trim(cast(cast(500.000 as integer) as char(5)))))
||trim(cast(cast(500.000 as integer) as char(5)))

Substitute col_name for '500.000'

 

[arzoocbe]

Thanks rrrkrishna
your code works for both RPAD and LPAD

-arzoo