Oracle函数总结：字符函数

oracle函数可以分为基本函数和分析函数两大类，在联机事务处理系统中，基本函数的使用非常多；而在以数据仓库为基础的数据分析中，
除了基本函数，也会用到分析函数。

---

1.ASCII(c1):c1表示一个字符串，此函数返回c1首字母及特殊字符的ASCII码，其逆函数为CHR().

SQL> select ASCII('a') ascii_a from dual;

   ASCII_A
----------
        97

2.CHR(i):i表示一个数字，该函数返回十进制表示的字符.

SQL> select CHR(97) from dual;
CHR(97)
-------
a

3.CONCAT(c1,c2):c1,c2为字符串，此函数将c2连接到c1后面:
示例：

SQL> select concat('abc','def') from dual;

CONCAT('ABC','DEF')
-------------------
abcdef

当c1为null或‘’时，返回c2的值；当c2为null或‘’时，返回c1的值:

SQL> select concat('abc',null) from dual;

CONCAT('ABC',NULL)
------------------
abc

SQL> select concat('abc','') from dual;

CONCAT('ABC','')
----------------
abc

SQL> select concat('','def') from dual;

CONCAT('','DEF')
----------------
def

SQL> select concat(null,'def') from dual;

CONCAT(NULL,'DEF')
------------------
def

4.INITCAP(c1)
其中，c1为字符串。该函数将每个单词的首字母大小，其余字母小写反回，单词由空格、控制字符和标点符号限制。示例：

SQL> select INITCAP('abc,def ghi') init from dual;

INIT
-----------
Abc,Def Ghi

5.INSTR(c1,c2[,i[,j]])
c1,c2为字符串，i,j为整数。该函数返回从c1的第i个字符开始，c2在c1中第j次出现的位置。如果搜索不到返回0,；如果i为负数，则从右向左搜索，但是位置计算还是从左到右，i和j默认值都为1.例如：

SQL> select instr('mississ','ss')i from dual;
         I
----------
         3
SQL> select instr('mississ','ss',4,1)i from dual; 
         I
----------
         6

SQL> select instr('mississ','ss',4,2)i from dual;
         I
----------
         0
SQL> select instr('mississ','ss',-4,1)i from dual;
         I
----------
         3

6.INSTRB(c1,c2[,i[,j]])
与instr()函数一样，只是这里返回的是字节，对于单字节，INSTRB()的效果等于instr().例如：

SQL>  select instrb('今天星期天','星')i from dual;

         I
----------
         5

7.LENGTH(c1)
如果c1为字符串，返回c1的长度，如果c1为null或者‘’返回null.例如：

SQL> select length('') from dual;

LENGTH('')
----------

SQL> select length(null) from dual;

LENGTH(NULL)
------------

SQL> select length('abcd') from dual;

LENGTH('ABCD')
--------------
             4

8.lENGTHB()
与length()一样，只是返回字节长度。

9.LOWER(c)
返回c的小写字符。

10.LPAD(c1,i,c2)
c1,c2为字符串，i为整数。在c1的左侧用c2字符串补足i长度(这里的补足指的是函数返回的字符串为长度i)，c2的默认值为单空格：

SQL> select lpad('abc',5) from dual;
LPAD('ABC',5)
-------------
  abc

如果i小于c1的长度，那么在c1的基础上截取i的长度：

SQL> select lpad('abc',1) from dual;

LPAD('ABC',1)
-------------
a

11.LTRIM(c1[,c2])
将c1最左边的字符去掉，使其第一个字符不在c2中，如果没有c2,则c1不会改变(但是会去除左边的空格)：

SQL> select ltrim('abc','ab') from dual;

LTRIM('ABC','AB')
-----------------
c

SQL> select ltrim('abc','ac') from dual;

LTRIM('ABC','AC')
-----------------
bc

SQL> select ltrim('   abc',' ') from dual;

LTRIM('ABC','')
---------------
abc

12.RPAD(c1,ic2)
c1,c2为字符串，i为整数。在c1的右侧侧用c2字符串补足i长度(这里的补足指的是函数返回的字符串为长度i)，c2的默认值为单空格，如果i小于c1的长度，那么在c1的基础上截取i的长度，与LPAD类似。

13.RTRIM(c1[,c2])
将c1最右边的字符去掉，使其第一个字符不在c2中，如果没有c2,则c1不会改变(但是会去除右边的空格)，与LTRIM()类似。

14.REPLACE(c1,[,c2,c3])
c1,c2,c3都为字符串，此函数用c3替换c1中的c2，字符级别替换.例如：

SQL> select replace('abcdef','bc','123') from dual;

REPLACE('ABCDEF','BC','123')
----------------------------
a123def

如果c3为‘’或者null，则被替换为空：

SQL> select replace('abcdef','bc','') from dual;

REPLACE('ABCDEF','BC','')
-------------------------
adef
SQL> select replace('abcdef','bc',null) from dual;

REPLACE('ABCDEF','BC',NULL)
---------------------------
adef

如果c2为‘’或null，则返回c1.
如果在c1中匹配不到c2,则不执行替换,这里要与TRANSLATE()函数做区分。

15.SUBSTR(c,i[,j])
c为字符串，i,j为整数。从c1的第i位开始截取长度为j的字符串，如果j为空，则截取全部。

16SUBSTRB(c,i[,j])
用法与SUBSTR相同，只是i,j以字节计算。

17.SOUNDEX(c1)
返回字符串参数的语音表示形式，相对于比较一些读音相同，但是拼写不同的单词是非常有用的。

计算语音的算法：

1.保留字符串首字母，但删除a、e、h、i、o、w、y
2.将下表中的数字赋给相对应的字母
 (1) 1：b、f、p、v
 (2) 2：c、g、k、q、s、x、z
 (3) 3：d、t
 (4) 4：l
 (5) 5：m、n
 (6) 6：r
3. 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个
4.只返回前4个字节，不够用0填充 

Oecle官网解释：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions148.htm

18.TRANSLATE(c1,c2,c3)
将c1中与c2相同的字符串用c3替换，字符串级别替换。这里需要注意如果在c1中没有匹配到c2,则会根据c2中单个字符跟c1中字符匹配，分别替换：

SQL> select TRANSLATE('abcdef','fc','aa') from dual;

TRANSLATE('ABCDEF','FC','AA')
-----------------------------
abadea

19.TRIM(c1 from c2)
用于从源字符串中截取特定字符串，c1为要截去的字符，c2为源字符串。注意：c1只能是单个字符，该函数常用于去空格。

SQL> select trim(' ' from '   abcdef  ') from dual;

TRIM(''FROM'ABCDEF')
--------------------
abcdef

20.UPPER(c)
返回c的大写。

21.NLS_SORT(column_name,’nls_param’)
该函数用于按特定的语言要求进行排序，其中，colum_name为排序的列名，nls_param的格式为‘nls_sort=sort’,排序规则：

Oracle9i之前，中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。

1、设置NLS_SORT参数值

SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序

SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序

SCHINESE_PINYIN_M 按照拼音排序

BINARY_CI 忽略大小写的排序

2、Session级别的设置，修改ORACLE字段的默认排序方式：

按拼音：alter session set nls_sort = SCHINESE_PINYIN_M;

按笔画：alter session set nls_sort = SCHINESE_STROKE_M;

按偏旁：alter session set nls_sort = SCHINESE_RADICAL_M；

忽略大小写：alter session set nls_sort = BINARY_CI；

示例：

select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M'); 

使用注意点，供参考
http://www.cnblogs.com/yanrongpi/archive/2008/04/11/1149144.html

22.REGEXP_REPLACE(source_string,pattern[,replace_string[,position[,occurrence[,match_parameter]]]])
该函数为Oracle10g新增函数，它扩展了replace函数的功能，用于按照特定表达式的规则替换字符串，其中参数含义依次是：

1、待匹配的字符串
2、正则表达式
3、替换的字符
4、标识从第几个字符开始正则表达式匹配。（默认为1）
5、标识第几个匹配组。（默认为全部都替换掉）
6、取值范围：
  i：大小写不敏感；
  c：大小写敏感；（默认）
  n：不匹配换行符号；
  m：多行模式；
  x：扩展模式，忽略正则表达式中的空白字符。

例如：

SQL> select REGEXP_REPLACE('MississipSi', 'si', 'SI', 1, 0, 'i') from dual;

REGEXP_REPLACE('MISSISSIPSI','
------------------------------
MisSIsSIpSI

23.REGEXP_SUBSTR(source_string,pattern[,position[,occurrence[,match_parameter]]])
该函数为Oracle10g新增函数，它扩展了substr函数的功能，用于按照特定表达式的规则返回字符串的子串，其中参数含义依次是：

1、待匹配的字符串
2、正则表达式
3、标识从第几个字符开始正则表达式匹配。（默认为1）
4、标识第几个匹配组。（默认为全部都替换掉）
5、取值范围：
  i：大小写不敏感；
  c：大小写敏感；（默认）
  n：不匹配换行符号；
  m：多行模式；
  x：扩展模式，忽略正则表达式中的空白字符。

例如：

--1、查询使用正则分割后的第一个值，也就是34 
SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL;  
--结果是：34   

--2、查询使用正则分割后的最后一个值，也就是23SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,3,'i') AS STR FROM DUAL;  
--结果是：-23