format an Decimal output with leading zeros URGENT 1

DFW1999 · Mar 26, 2004

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 · Mar 26, 2004

Hello All,

I got the following solution:

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

Thanks

rrrkrishnan · Mar 26, 2004

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 · Mar 31, 2004

Thanks rrrkrishna
your code works for both RPAD and LPAD

-arzoo

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

format an Decimal output with leading zeros URGENT 1

DFW1999

Programmer

DFW1999

Programmer

rrrkrishnan

Programmer

arzoocbe

Programmer

Similar threads

Part and Inventory Search

Sponsor