SQL & PL/SQL

Oracle 10 g

Hi Gurus

I appreciate if someone help me out and suggest me some better option to do this task...

I wrote the following code in which I have to format by using leading zeros, I wrote the following query for that purpose but I'm wondering that if there is some short query to do the same task, please see below my code..

WITH datum AS

(

SELECT 1 num_format FROM dual

UNION ALL

SELECT 10 FROM dual

UNION ALL

SELECT 100 FROM dual

UNION ALL

SELECT 1000 FROM dual

)

SELECT num_format,CASE WHEN LENGTH(num_format)=1 THEN '000'||num_format

                       WHEN LENGTH(num_format)=2 THEN '00'||num_format

                       WHEN LENGTH(num_format)=3 THEN '0'||num_format

                       WHEN LENGTH(num_format)=4 THEN to_char(num_format)

END MODI_FORMAT

FROM datum;

Result

NUM_FORMAT, MODI_FORMAT

1                0001

10              0010

100            0100

1000         1000

Regards

Shumail