SQL & PL/SQL

Hi ,

I try to use lpad function to add zeroes a number field but it does not work. It only works numbers except '0'. I run this queries on 11gr2 and 10gr2 but get same results.

lpad function is for characters but Is there any way to do it?

Here my examples;

SQL> create table samp_tab (id number);
Table created.

SQL> insert into samp_tab (id) values (lpad('123',9,'0'));
1 row created.

SQL> select * from samp_tab;

        ID
----------
       123

----- I've tried to lead another number and it works well
SQL> insert into samp_tab (id) values (lpad('123',9,'5'));


1 row created.



SQL> select * from samp_tab;


        ID
----------
       123
 555555123



SQL> insert into samp_tab (id) values (lpad('123',9,'8'));


1 row created.


SQL> select * from samp_tab;


        ID
----------
       123
 555555123
 888888123



-- I 've tried without quotes:

SQL> insert into samp_tab (id) values (lpad('123',9,0));


1 row created.


SQL> select * from samp_tab;


        ID
----------
       123
 555555123
 888888123
       123



-- rpad functions works well.
SQL> insert into samp_tab (id) values (rpad('123',9,0));


1 row created.



SQL> select * from samp_tab;


        ID
----------
       123
 555555123
 888888123
       123
 123000000


SQL>

Any help appreciated

Regards.