--创建测试数据 declare v_count1 int :=0; v_sql varchar2(1000); Begin select count(*) into v_count1 from user_tables where table_name='test1'; if v_count1=0 then v_sql :=' create table test1 ( id int, monety number(4) ) '; execute immediate(v_sql); end if; End; insert into test1 values(1,1000); insert into test1 values(2,2000); insert into test1 values(3,3000); insert into test1 values(4,4002); commit; --查询语句 select lpad(monety,10,'0') from test1;--结果: 1 0000001000 2 0000002000 3 0000003000 4 0000004002
sql: select lpad(4500,10,'0') from dual 结果:0000004500
select To_Char(4500, '0000000000') from dual;这样写就行了。你想几位都行。。
SELECT TO_CHAR(4500,'fm0999999999') FROM DUAL
SELECT TO_CHAR(4500,'fm0999999999') FROM DUAL 使用这个方法就OK
--左填充 select lpad(12345,10,'0') from dual;--右填充 select rpad('1234.',10,'0') from dual;
实测数据:CREATE TABLE T85 ( ID NUMBER(4), Money NUMBER(20) ); insert into T85 values(1, 1000); insert into T85 values(2, 2000); insert into T85 values(3, 3000); insert into T85 values(4, 4002);实测结果:
--创建测试数据
declare v_count1 int :=0;
v_sql varchar2(1000);
Begin
select count(*) into v_count1 from user_tables where table_name='test1';
if v_count1=0 then
v_sql :='
create table test1
(
id int,
monety number(4)
)
';
execute immediate(v_sql);
end if;
End;
insert into test1 values(1,1000);
insert into test1 values(2,2000);
insert into test1 values(3,3000);
insert into test1 values(4,4002);
commit;
--查询语句
select lpad(monety,10,'0') from test1;--结果:
1 0000001000
2 0000002000
3 0000003000
4 0000004002
select lpad(4500,10,'0') from dual 结果:0000004500
SELECT TO_CHAR(4500,'fm0999999999') FROM DUAL
使用这个方法就OK
--左填充
select lpad(12345,10,'0') from dual;--右填充
select rpad('1234.',10,'0') from dual;
(
ID NUMBER(4),
Money NUMBER(20)
);
insert into T85 values(1, 1000);
insert into T85 values(2, 2000);
insert into T85 values(3, 3000);
insert into T85 values(4, 4002);实测结果: