17,377
社区成员
发帖
与我相关
我的任务
分享
select substr('00000'||'1',-5,5) from dual;
CREATE OR REPLACE FUNCTION GET_SHOW_ZERO_SECOND(VAL VARCHAR) RETURN VARCHAR IS
RESULT VARCHAR(50);
BEGIN
SELECT decode(Instr(VAL,'.'),0,TO_CHAR(VAL,'FM999999990.90'),to_char(VAL,'FM999999990.90')) INTO RESULT FROM DUAL;
RETURN RESULT;
END;
select lpad('1', 5, '0') from dual;
LPAD('1',5,'0')
---------------
00001
[/Quote]
SCOTT@oamis>>select rpad('1',8,'0') from dual;
RPAD('1'
--------
10000000
已用时间: 00: 00: 00.00
SQL> select lpad(sal,5,0),sal from emp;
--lpad(sal,5,0)是sal显示是为5位不够往左边补0 如果是右边就用rpad()
LPAD(SAL,5 SAL
---------- ----------
00800 800
01600 1600
01250 1250
02975 2975
01250 1250
02850 2850
02450 2450
03000 3000
05000 5000
01500 1500
01100 1100
LPAD(SAL,5 SAL
---------- ----------
00950 950
03000 3000
01300 1300
SQL> column sal heading "newsal" format 09999;--设置列的格式输出
SQL> select sal from emp;
newsal
------
00800
01600
01250
02975
01250
02850
02450
03000
05000
01500
01100
newsal
------
00950
03000
01300