oracle常用函数

1、ASCII     返回与制定的字符对应的十进制数；

    SQL> select ascii('A') A,ascii('B') B, ascii('1') one,ascii('2') two from dual;
 
                  A          B        ONE        TWO
           ---------- ---------- ---------- ----------
                 65         66         49         50

2、CHR 给出的整数，返回对应的字符；

SQL> select chr('65') chr65,chr('123') chr123 from dual;
 
CHR65 CHR123
----- ------
A     {

3、concat  连接两个字符串    等同于||；

SQL> select concat(ename,'是外星人')|| '吗'  ename from emp;
 
ENAME
----------------------------------------
SMITH是外星人吗
ALLEN是外星人吗

4、INITCAP  返回字符串并将字符串的第一个字符变为大写，其余变为小写；

SQL> select initcap(ename) from emp;
 
INITCAP(ENAME)
------------------------------
Smith
Allen

5、INSTR(a,b,c,d)   在一个字符串中搜索制定的字符，返回发现制定字符的位置；

  a    表示被搜索的字符

b    表示希望搜索的字符

c    搜索的开始位置，默认为1

d    出现的位置，默认为1

SQL> select instr('qweasdzxcwefger','w',1,2) from dual;
 
INSTR('QWEASDZXCWEFGER','W',1,
------------------------------
                            10

7、LENGTH    返回字符串的长度

SQL>  select length('你好吗abc') from dual;
 
    LENGTH('你好吗ABC')
-------------------
                  6

8、LOWER返回字符串，并将所有的字符小写

SQL> select lower('LONGyun朋') from dual;
 
LOWER('LONGYUN朋')
------------------
longyun朋

8、UPPER 返回字符串，并将所有的字符大写

SQL> select upper('longYUN朋') from dual;
 
UPPER('LONGYUN朋')
------------------
LONGYUN朋

9、RPAD和LPAD（ 粘帖字符）

RPAD 在列的右边粘帖字符

例：

SQL> SELECT RPAD('Page 1',15,'*') "LPAD example" FROM DUAL;
 
LPAD example
---------------
Page 1*********

LPAD 在列的左边粘帖字符

LPAD（char1,n,char2）

char1： 为显示的部分

n：为显示的字符

char2 ：如果char1长度小于n时用char2补充左边

例：SQL>  SELECT LPAD('Page 1',15,'*') "LPAD example" FROM DUAL;
 
   LPAD example
---------------
*********Page 1

10、LTRIM 和RIRIM

 LTRIM 删除左边出现的字符串

 RTRIM  删除右边出现的字符串

SQL> SELECT rtrim(ltrim('oooolongyunpengxxx','o'),'x') "LTRIM example" FROM DUAL;
 
LTRIM example
-------------
longyunpeng    

11、SUBSTR(string,start,count)  取字符串，从start开始，取count个

(1) 如果是正值，则Oracle数从字符串的第一个字符开始查找。

SQL> SELECT substr('ABCDEFG',3,4) "Substring" FROM DUAL;
 
Substring
---------
CDEF

(2) 当位置是负的，那么Oracle数落后从字符串的末尾。

SQL> SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;
 
Substring
---------
CDEF

12、REPLACE(string,s1,s2)

string 希望被替换的字符或变量

s1 被替换的字符串

s2 要替换的字符串

SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;
 
Changes
------------------------
BLACK and BLUE

13、SOUNDEX 返回一个与给定的字符串读音相同的字符串，只支持英文。

SQL> SELECT last_name, first_name FROM hr.employees WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');

LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey
Smith      William

14.TRIM('S' from ) 去掉指定 字符串或是数值前后的某些字符或数值

SELECT TRIM (0 FROM 0009872348900) "TRIM Example"  FROM DUAL;
TRIM Example
------------
    98723489

15、ABS 返回指定的绝对值

SQL> select abs(-10),abs(10) from dual;
 
  ABS(-10)    ABS(10)
---------- ----------
        10         10

16、FLOOR对给定的数字向下取整

SQL> select floor(3.6666) from dual;
 
FLOOR(3.6666)
-------------
            3

17、ROUND按照四舍五入取值

SQL> select round(52.3),round(52.5) from dual;
 
ROUND(52.3) ROUND(52.5)
----------- -----------
         52          53

18、ADD_MONTHS    增加或减去月份

SQL> select to_char(add_months(sysdate,2),'yyyymm'),to_char(sysdate,'yyyymm'),to_char(add_months(sysdate,-2),'yyyymm') from dual;
 
TO_CHAR(ADD_MONTHS(SYSDATE,2), TO_CHAR(SYSDATE,'YYYYMM') TO_CHAR(ADD_MONTHS(SYSDATE,-2)
------------------------------ ------------------------- ------------------------------
201405                         201403                    201401

19、LAST_DAY 返回日期的最后一天

SQL> select last_day(add_months(sysdate,2)),to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual;
 
LAST_DAY(ADD_MONTHS(SYSDATE,2) TO_CHAR(SYSDATE,'YYYY-MM-DDHH:
------------------------------ ------------------------------
2014-5-31 21:21:53             2014-03-11 09:03:53

20、MONTHS_BETWEEN(DATE2,DATE1)   给出data2-date1之间有多少个月

SQL> select months_between(sysdate,to_date('2013-11-11','yyyy-mm-dd')) from dual;
 
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
                             4 

21、NEXT_DAY （date,char） 返回离date最近的char是多少号

SQL> select next_day(sysdate,'星期一') from dual;
 
NEXT_DAY(SYSDATE,'星期一')
--------------------------
2014-5-5 23:23:36

22、DECODE(expr,search,result,search,result.....,default)  

如果expr与search匹配，Oracle将返回相应的结果。如果没有发现匹配，则Oracle将返回默认。如果默认是略，Oracle将返回null。

SQL> select decode(3,1,'1号',2,'2号',3,'3号') decode from dual;
DECODE
------
3号

23、NVL(expr1,expr2) 

如果expr1的值为空，则返回expr2 NVL。如果expr1的值不为空，则返回值expr1 NVL。

SQL> select nvl('','Not Applicable') from dual;
NVL('','NOTAPPLICABLE')
-----------------------
Not Applicable
SQL>  select nvl('abcd','Not Applicable') from dual;
NVL('ABCD','NOTAPPLICABLE')
---------------------------
abcd