oracle 部分单行函数使用示例

SQL> SELECT INSTR('Mississippi', 'i', 3, 3) a,INSTR('Mississippi', 'i', -3, 3) b,INSTR('Mississippi', 'i', 3) c,INSTR('Mississippi', 'i') d   FROM DUAL;
 
         A          B          C          D
---------- ---------- ---------- ----------
        11          2          5          2
SQL> SELECT LPAD('answer', 10, 'w'),LPAD('answer', 3, 'w'),LPAD('answer', 6, 'w') FROM DUAL;
 
LPAD('ANSWER',10,'W') LPAD('ANSWER',3,'W') LPAD('ANSWER',6,'W')
--------------------- -------------------- --------------------
wwwwanswer            ans                  answer
SQL> select LTRIM('Mississippi','Mis') from dual;
 
LTRIM('MISSISSIPPI','MIS')
--------------------------
ppi
SQL> SELECT RPAD('answer', 10, 'w') a,LPAD('answer', 3, 'w') b,LPAD('answer', 6, 'w') c FROM DUAL;
 
A          B   C
---------- --- ------
answerwwww ans answer
SQL> SELECT RTRIM('Mississippi','Mis') FROM dual;
 
RTRIM('MISSISSIPPI','MIS')
--------------------------
Mississipp
SQL> SELECT REPLACE('uptown','up','down') FROM dual;
 
REPLACE('UPTOWN','UP','DOWN')
-----------------------------
downtown
SQL> SELECT SUBSTR('Message',1,4) FROM dual;
 
SUBSTR('MESSAGE',1,4)
---------------------
Mess
SQL> SELECT TRANSLATE('fumble','uf','ar') test FROM dual;
 
TEST
------
ramble
SQL> SELECT TRIM('d' FROM ' space padded') a,TRIM(' space padded ') b  from dual;
 
A            B
------------ ------------
 space padde space padded
SQL> SELECT FLOOR(2.5),ceil(2.4) FROM dual;
 
FLOOR(2.5)  CEIL(2.4)
---------- ----------
         2          3
SQL> SELECT MOD(5,3) FROM dual;
 
  MOD(5,3)
----------
         2
SQL> SELECT trunc(5.75), trunc(5.75,1), trunc(5.75,-1) FROM dual;
 
TRUNC(5.75) TRUNC(5.75,1) TRUNC(5.75,-1)
----------- ------------- --------------
          5           5.7              0
SQL> SELECT round(5.75) a, round(5.75,1) b, round(5.74,1) c, round(5.75,-1 )d, round(14.75,-1) e FROM dual;
 
         A          B          C          D          E
---------- ---------- ---------- ---------- ----------
         6        5.8        5.7         10         10
SQL> SELECT SIGN(10-10),SIGN(10-11),SIGN(10-9),SIGN(10-0) FROM dual;
 
SIGN(10-10) SIGN(10-11) SIGN(10-9) SIGN(10-0)
----------- ----------- ---------- ----------
          0          -1          1          1
SQL> SELECT chr(63),ASCii('?'),chr(39),ascii('''')  FROM dual;
 
CHR(63) ASCII('?') CHR(39) ASCII('''')
------- ---------- ------- -----------
?               63 '                39
SQL> SELECT LENGTH('Ip so Facto') FROM dual;
 
LENGTH('IPSOFACTO')
-------------------
                 11
SQL> SELECT SYSDATE,ADD_MONTHS(SYSDATE,1) a,ADD_MONTHS(SYSDATE,1.5) b,ADD_MONTHS(SYSDATE,-1) c FROM dual;
 
SYSDATE     A           B           C
----------- ----------- ----------- -----------
2011-9-15 1 2011-10-15  2011-10-15  2011-8-15 1
SQL> SELECT LAST_DAY(SYSDATE) FROM dual;
 
LAST_DAY(SYSDATE)
-----------------
2011-9-30 14:50:3
SQL> SELECT MONTHS_BETWEEN(SYSDATE,SYSDATE+31) FROM dual;
 
MONTHS_BETWEEN(SYSDATE,SYSDATE
------------------------------
             -1.03225806451613
SQL> SELECT ROUND(SYSDATE) a,ROUND(SYSDATE,'YYYY') b,ROUND(SYSDATE,'mm') c,ROUND(SYSDATE,'DD') d,round(SYSDATE,'HH')e,round(SYSDATE,'MI') f/*,round(SYSDATE,'ss')*/FROM dual;
 
A           B           C           D           E           F
----------- ----------- ----------- ----------- ----------- -----------
2011-9-16   2012-1-1    2011-9-1    2011-9-16   2011-9-15 1 2011-9-15 1
SQL> SELECT TRUNC(SYSDATE) a,TRUNC(SYSDATE,'yyyy') b,TRUNC(SYSDATE,'mm') c,TRUNC(SYSDATE,'DD') d,TRUNC(SYSDATE,'HH')e,TRUNC(SYSDATE,'MI') f/*,TRUNC(SYSDATE,'ss')*/FROM dual;
 
A           B           C           D           E           F
----------- ----------- ----------- ----------- ----------- -----------
2011-9-15   2011-1-1    2011-9-1    2011-9-15   2011-9-15 1 2011-9-15 1
SQL> /*9 表示有效位数
  2  0 数字前用0补足位数
  3  B 用空格补足位数
  4  $ 在数字前加美元号
  5  L 在数字前加本地货币符号
  6  . 小数点位置
  7  , 分格符位置
  8  MI 负数时在最后边加负号
  9  PR 负数用<>括起来
 10  S 在前面加符号
 11  V 把原数字乘以10的N次方(N为V后面的位数)
 12  
 13  */
 14  SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mm:ss') a,
 15         TO_CHAR(99.45, '000.999') b,
 16         TO_CHAR(99.45, 'L900.999') c,
 17         TO_CHAR(9999.45, 's00,00.90') d,
 18         TO_CHAR(99, '0999.00PR') e,
 19         TO_CHAR(-99.125, '900.00MI') f,
 20         TO_CHAR(-99.124, '000.00PR') g,
 21         TO_CHAR(-99.124, '9999v999') h
 22    FROM DUAL;
 
A                   B        C                  D         E         F       G        H
------------------- -------- ------------------ --------- --------- ------- -------- --------
2011-09-15 14:09:37  099.450           ￥99.450 +99,99.45  0099.00   99.13- <099.12>   -99124
SQL> SELECT TO_DATE(TO_CHAR(SYSDATE,'yyyy-mm-dd'), 'yyyy-mm-dd') FROM DUAL;
 
TO_DATE(TO_CHAR(SYSDATE,'YYYY-
------------------------------
2011-9-15
SQL> SELECT to_number('23'),TO_NUMBER('0A', 'XX') ,TO_NUMBER('FF', 'XXxx') FROM dual;
 
TO_NUMBER('23') TO_NUMBER('0A','XX') TO_NUMBER('FF','XXXX')
--------------- -------------------- ----------------------
             23                   10                    255
SQL> --返回唯一标示当前数据库用户的整数
SQL> SELECT UID FROM dual;
 
       UID
----------
        68