Oracle数据库中的内置函数汇总（1）

转载请标明出处，否则将追究一切法律责任！

第一部分：字符型函数

1、ASCII（x）：返回字符x的ASCII码。

eg：

SQL> select ascII(3) from dual;

  ASCII(3)
----------
        51

SQL> select ASCII('A') from dual;

ASCII('A')
----------
        65

2、CHR（x）：返回ASCII值为x的字符

SQL> select chr(51) from dual;

C
-
3

3、CONCAT（x,y）：返回将x与y连接之后的新字符串

SQL> select concat('xfsdf','-yd') from dual;

CONCAT('
--------
xfsdf-yd

4、INITCAP（x）：转换每个单词的首字母大写，其他字母小写，并返回一个新的字符串

SQL> select initcap(' this is My name! ') as "initcap( )" from dual;

initcap( )
----------------
This Is My Name!

5、INSTR（x，find_string[，start] [，occurrence]）：查询find_string子字符串在字符串x中出现的位置，可选的是可以为start提供一个起始值，occurrence指定出现位置的次数，默认是第1次出现时的位置，通过指定该参数可以指定其出现的不同次数。

SQL> select instr('this is my name is henry and age is 20.','is') as "location" from dual;

  location              //第一次出现is是在单词this中位置是3  从1开始数
----------
         3

SQL> select instr('this is my name is henry and age is 20.','is',10,2) as "location" from dual;

  location                //从第10个位置（my中的y）开始数，第2次出现“is”时返回的总位置（空格也算）
----------
        34

6、LENGTH（x）：返回字符串x的长度

select length(data) from tbdipdi where rownum<=8; 

LENGTH(DATA)
------------
          61
          61
          61
          60
          60
          61
          61
         145

7、LOWER（x）：将字符串转换成小写并返回

      UPPER（x）：将字符串转换成大写形式并返回

eg：

SQL> select LOWER('this IS my Name') from dual;

LOWER('THISISMY
---------------
this is my name

eg：

SQL> select UPPER(data) from tbdipdi where rownum<=4;

UPPER(DATA)
--------------------------------------------------------------------------------
TXM_IDADJ$$$N$$$C20170304706013$$$31$$$HRB400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706014$$$31$$$HRB400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706015$$$31$$$HRB400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706016$$$31$$$HRB400$$$3185.000$$$N

8、LPAD（x，width[，pad_string]）：填充字符值左调节到width字符位置的总宽度，指定pad_string将表示使用pad_string进行填充，默认使用空白进行填充

RPAD（x，width[，pad_string]）：右填充到width字符位置的总宽度。

SQL> select lpad('abcd24',10,'#') as "LPAD( )" from dual;

LPAD( )
----------
####abcd24

SQL> select rpad('abcd24sdfas345',7,'#') as "RPAD( )" from dual;         //如果长度超过width则截断

RPAD( )
-------
abcd24s

9、LTRIM（x[，trim_string]）：去除字符串左侧的空白，如果指定了trim_string，将去除指定的字符串，默认去除左侧空白字符。

      RTRIM（x[，trim_string]）：去除字符串右侧的空白。

SQL> select rtrim('    as345    ') as "RTRIM( )" from dual;

RTRIM( )
---------
    as345

SQL> select ltrim('    fsaafs345fsa    ','  fsa') as "LTRIM( )" from dual;        //trim_sting包含的‘ ’、f、s、a、四个符号从左侧开始截取，一直到第一个不包含在trim_string中的字符为止！

LTRIM( )
----------
345fsa

10、NANVL（x，value）：从10g开始引入，如果x匹配一个NAN特定的值（非数字），则返回value的值，否则返回x

11、nvl（x，value）：如果x为NULL，则返回value，否则返回x

SQL> select id,name,nvl(score,999999) from table01;

        ID NAME                 NVL(SCORE,999999)
---------- -------------------- -----------------
         5 henry                               68
         3 hhh                                 33
         6 madak                           999999
         1 zhang3                              78
         2 li                                  88
         4 wang                                60
         4 ma                                  54
         2 huang                              100
         2 zhao                            999999

12、nvl2（x，value1，value2）：如果x不为NULL，则返回value1，否则返回value2

SQL> select id,name,nvl2(score,1,0) from table01;


        ID NAME                 NVL2(SCORE,1,0)
---------- -------------------- ---------------
         5 henry                              1
         3 hhh                                1
         6 madak                              0
         1 zhang3                             1
         2 li                                 1
         4 wang                               1
         4 ma                                 1
         2 huang                              1
         2 zhao                               0

13、REPLACE（x，search_string，replace_string）：搜索字符串x中search_string子字符串，并且使用replace_string替换该字符串

SQL> select data from tbdipdi where rownum<=4;
DATA
--------------------------------------------------------------------------------
TXM_IDADJ$$$N$$$C20170304706013$$$31$$$HRB400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706014$$$31$$$HRB400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706015$$$31$$$HRB400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706016$$$31$$$HRB400$$$3185.000$$$N

SQL> select replace(data,'HRB','HWR') from tbdipdi where rownum<=4;
REPLACE(DATA,'HRB','HWR')
--------------------------------------------------------------------------------
TXM_IDADJ$$$N$$$C20170304706013$$$31$$$HWR400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706014$$$31$$$HWR400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706015$$$31$$$HWR400$$$3185.000$$$N
TXM_IDADJ$$$N$$$C20170304706016$$$31$$$HWR400$$$3185.000$$$N

14、SOUNDEX（x）：返回字符串参数的语音表示形式，相对于比较一些读音相同，但拼写不同的单词是非常有用的

15、substr（x，start [，length]）：返回x字符串的一个子串，start指定起始位置，length可以指定长度进行截取

SQL> select substr(data,17,15) from tbdipdi where rownum<=4;
//从第17个开始截取长度为15的子串（默认从1开始数）

SUBSTR(DATA,17,15)
------------------------------------------------------------
C20170304706013
C20170304706014
C20170304706015
C20170304706016

16、TRIM（[trim_char FROM] x）：左右同时截取字符串x，trim_char指定要截除的字符，如果没有指定，默认截除空白字符串

17、TRANSLATE（string，fromstr，tostr）：使用tostr字符串替换掉在string中出现的所有的fromstr字符串，功能与replace相似，只是translate函数中的tostr参数不能缺少，更不能为空白字符串，Oracle会将空白字符串理解为NULL，因此translate的结果也将为NULL

SQL> select translate('fsdfs35sdf','sdf','') from dual;

T
-


SQL> select replace('fsdfs35sdf','sdf','') from dual;

REPL
----
fs35