ORACLE常用数值函数、转换函数、字符串函数

永不磨灭的番号 高清,红茶论坛,teemzone官网

本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思的一些函数。分二类介绍，分别是：
　　著名函数篇　－经常用到的函数
　　非著名函数篇－即虽然很少用到，但某些情况下却很实用

注：n表示数字型，c表示字符型，d表示日期型，[]表示内中参数可被忽略，fmt表示格式。

　　单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。
(一).数值型函数(number functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：cos, cosh, exp, ln, log, sin, sinh, sqrt, tan, and tanh 支持36位小数点。acos, asin, atan, and atan2支持30位小数点。

1、mod(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。
例如：select mod(24,5) from dual;

2、round(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。
例如：select round(23.56),round(23.56,1),round(23.56,-1) from dual;

3、trunc(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
例如：select trunc(23.56),trunc(23.56,1),trunc(23.56,-1) from dual;

(二).字符型函数返回字符值(character functions returning character values)
　　该类函数返回与输入类型相同的类型。
 返回的char类型值长度不超过2000字节；
 返回的vchar2类型值长度不超过4000字节；
如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。

 返回的clob类型值长度不超过4g；
对于clob类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。

1、lower(c) 将指定字符串内字符变为小写，支持char,varchar2,nchar,nvarchar2,clob,nclob类型
例如：select lower('what is this') from dual;

2、upper(c) 将指定字符串内字符变为大写，支持char,varchar2,nchar,nvarchar2,clob,nclob类型
例如：select upper('what is this') from dual;

3、lpad(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点：
 如果n<c1.length则从右到左截取指定长度返回；
 如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；
 如果n>c1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回；
例如：select lpad('what is this',5),lpad('what is this',25),lpad('what is this',25,'-') from dual;
最后大家再猜一猜，如果n<0，结果会怎么样

4、rpad(c1,n[,c2]) 返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；
例如：select rpad('what is this',5),rpad('what is this',25),rpad('what is this',25,'-') from dual;

5、trim([[leading||trailing||both] c2 from] c1) 哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。
看起来很复杂，理解起来很简单：
 如果没有指定任何参数则oracle去除c1头尾空格
例如：select trim(' what is this ') from dual;
 如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)
例如：select trim('w' from 'what is this w w') from dual;
 如果指定了leading参数则会去掉c1头部c2
例如：select trim(leading 'w' from 'what is this w w') from dual;
 如果指定了trailing参数则会去掉c1尾部c2
例如：select trim(trailing 'w' from 'what is this w w') from dual;
 如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)
例如：select trim(both 'w' from 'what is this w w') from dual;

注意：c2长度=1

6、ltrim(c1[,c2]) 千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。
例如：select ltrim('wwhhhhhat is this w w','wh') from dual;

7、rtrim(c1,c2)与上同，不过方向相反
例如：select rtrim('wwhhhhhat is this w w','w w') from dual;

8、replace(c1,c2[,c3]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。
例如：select replace('wwhhhhhat is this w w','w','-') from dual;

9、soundex(c) 神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：
 保留字符串首字母，但删除a、e、h、i、o、w、y。
 将下表中的数字赋给相对应的字母：
1：b、f、p、v
2：c、g、k、q、s、x、z
3：d、t
4：l
5：m、n
6：r
 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个；
 只返回前4个字节，不够用0填充
例如：select soundex('dog'),soundex('boy') from dual;

10、substr(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度；
n2=截取的字符串长度，如果为空，默认截取到字符串结尾；
 如果n1=0 then n1=1
 如果n1>0，则oracle从左向右确认起始位置截取
例如：select substr('what is this',5,3) from dual;
 如果n1<0，则oracle从右向左数确认起始位置
例如：select substr('what is this',-5,3) from dual;
 如果n1>c1.length则返回空
例如：select substr('what is this',50,3) from dual;
然后再请你猜猜，如果n2<1，会如何返回值呢

11、translate(c1,c2,c3) 就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。什么是绝对匹配替换呢？简单的说，是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解，我们通过几具实例来说明：
例如：
select translate('what is this','','-') from dual;
select translate('what is this','-','') from dual;
结果都是空。来试试这个：
select translate('what is this',' ',' ') from dual;
再来看这个：
select translate('what is this','ait','-*') from dual;
是否明白了点呢？replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了？replace是替换，而translate则像是过滤

(三).字符型函数返回数字值(character functions returning number values)
本类函数支持所有的数据类型

1、instr(c1,c2[,n1[,n2]]) 返回c2在c1中位置
 c1:原字符串
 c2:要寻找的字符串
 n1:查询起始位置，正值表示从左到右，负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0
 n2:第几个匹配项。大于0
例如：select instr('abcdefg','e',-3) from dual;

2、length(c) 返回指定字符串的长度。如果
例如：select length('a123中') from dual;
猜猜select length('') from dual;的返回值是什么

(四).日期函数(datetime functions)
本类函数中，除months_between返回数值外，其它都将返回日期。

1、add_months() 返回指定日期月份+n之后的值，n可以为任何整数。
例如：select add_months(sysdate,12),add_months(sysdate,-12) from dual;

2、current_date 返回当前session所在时区的默认时间
例如：
sql> alter session set nls_date_format = 'mm-dd-yyyy' ;
sql> select current_date from dual;

3、sysdate 功能与上相同，返回当前session所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作oracle的工作机制写的很好，深入浅出)的短暂交流，我们认为current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。
例如：select sysdate,current_date from dual;

4、last_day(d) 返回指定时间所在月的最后一天
例如：select last_day(sysdate) from dual;

5、next_day(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。
例如：三思用的中文nt，nls_language值为simplified chinese
select next_day(sysdate,5) from dual;
select next_day(sysdate,'星期四') from dual;
两种方式都可以取到正确的返回，但是：
select next_day(sysdate,'thursday') from dual;
则会执行出错，提供你说周中的日无效，就是这个原因了。

6、months_between(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0
例如：
select months_between(sysdate, sysdate),
months_between(sysdate, add_months(sysdate, -1)),
months_between(sysdate, add_months(sysdate, 1))
from dual;

7、round(d[,fmt]) 前面讲数值型函数的时候介绍过round，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt参数，则默认返回距离指定日期最近的日期。
例如：select round(sysdate,'hh24') from dual;

8、trunc(d[,fmt]) 与前面介绍的数值型trunc原理相同，不过此处也是操作的日期型。
例如：select trunc(sysdate,'hh24') from dual;

(五).转换函数(conversion functions)
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

1、to_char() 本函数又可以分三小类，分别是
 转换字符->字符to_char(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型；
例如：select to_char('aabbcc') from dual;

 转换时间->字符to_char(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；
例如：select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 转换数值->字符to_char(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；
例如：select to_char(-100, 'l99g999d99mi') from dual;

2、to_date(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'j'则表示按照公元制(julian day)转换，c则必须为大于0并小于5373484的正整数。
例如：
select to_date(2454336, 'j') from dual;
select to_date('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') from dual;

为什么公元制的话，c的值必须不大于5373484呢？因为oracle的date类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句：
select to_char(to_date('9999-12-31','yyyy-mm-dd'),'j') from dual;

3、to_number(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
例如：select to_number('-100.00', '9g999d99') from dual;

(六).其它辅助函数(miscellaneous single-row functions)

1、decode(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。
毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

例如：select decode('a2','a1','true1','a2','true2','default') from dual;

2、greatest(n1,n2,...n) 返回序列中的最大值
例如：select greatest(15,5,75,8) "greatest" from dual;

3、least(n1,n2....n) 返回序列中的最小值
例如：select least(15,5,75,8) least from dual;

4、nullif(c1,c2)
nullif也是个很有意思的函数。逻辑等价于：case when c1 = c2 then null else c1 end
例如：select nullif('a','b'),nullif('a','a') from dual;

5、nvl(c1,c2) 逻辑等价于if c1 is null then c2 else c1 end。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。
例如：select nvl(null, '12') from dual;

6、nvl2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3
例如：select nvl2('a', 'b', 'c') isnull,nvl2(null, 'b', 'c') isnotnull from dual;

7、sys_connect_by_path(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。
例如：
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);

insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');

select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;

8、sys_context(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
oracle9i提供内置了一个命名空间userenv，描述了当前session的各项信息，其拥有下列参数：
 current_schema:当前模式名；
 current_user:当前用户；
 ip_address:当前客户端ip地址；
 os_user:当前客户端操作系统用户；
等等数十项，更详细的参数列还请大家直接参考oracle online documents

例如：select sys_context('userenv', 'session_user') from dual;
注：n表示数字型，c表示字符型，d表示日期型，[]表示内中参数可被忽略，fmt表示格式。

单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。
(一).数值型函数(number functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：cos, cosh, exp, ln, log, sin, sinh, sqrt, tan, and tanh 支持36位小数点。acos, asin, atan, and atan2支持30位小数点。

1、abs(n) 返回数字的绝对值
例如：select abs(-1000000.01) from dual;

2、cos(n) 返回n的余弦值
例如：select cos(-2) from dual;

3、acos(n) 反余弦函数，n between -1 and 1，返回值between 0 and pi。
例如：select acos(0.9) from dual;

4、bitand(n1,n2) 位与运算，这个太有意思了，虽然没想到可能用到哪里，详细说明一下：
假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，则结果是0001，转换成10进制数为1。
例如：select bitand(3,9) from dual;

5、ceil(n) 返回大于或等于n的最小的整数值
例如：select ceil(18.2) from dual;
考你一下，猜猜ceil(-18.2)的值会是什么呢

6、floor(n) 返回小于等于n的最大整数值
例如：select floor(2.2) from dual;
再猜猜floor(-2.2)的值会是什么呢

7、bin_to_num(n1,n2,....n) 二进制转向十进制
例如：select bin_to_num(1),bin_to_num(1,0),bin_to_num(1,1) from dual;

8、sin(n) 返回n的正玄值,n为弧度。
例如：select sin(10) from dual;

9、sinh(n) 返回n的双曲正玄值,n为弧度。
例如：select sinh(10) from dual;

10、asin(n) 反正玄函数，n between -1 and 1，返回值between pi/2 and -pi/2。
例如：select asin(0.8) from dual;

11、tan(n) 返回n的正切值,n为弧度
例如：select tan(0.8) from dual;

12、tanh(n) 返回n的双曲正切值,n为弧度
例如：select tanh(0.8) from dual;

13、atan(n) 反正切函数，n表示弧度，返回值between pi/2 and -pi/2。
例如：select atan(-444444.9999999) from dual;

14、exp(n) 返回e的n次幂，e = 2.71828183 ...
例如：select exp(3) from dual;

15、ln(n) 返回n的自然对数，n>0
例如：select ln(0.9) from dual;

16、log(n1,n2) 返回以n1为底n2的对数，n1 >0 and not 1 ，n2>0
例如：select log(1.1,2.2) from dual;

17、power(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值，不过如果m是负数，则n必须为整数
例如：select power(2.2,2.2) from dual;

18、sign(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.
例如：select sign(14),sign(-14),sign(0) from dual;

19、sqrt(n) 返回n的平方根,n为弧度。n>=0
例如：select sqrt(0.1) from dual;

(二).字符型函数返回字符值(character functions returning character values)
　　该类函数返回与输入类型相同的类型。
 返回的char类型值长度不超过2000字节；
 返回的vchar2类型值长度不超过4000字节；
如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。

 返回的clob类型值长度不超过4g；
对于clob类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。

1、chr(n[ using nchar_cs]) 返回指定数值在当前字符集中对应的字符
例如：select chr(95) from dual;

2、concat(c1,c2) 连接字符串，等同于||
例如：select concat('aa','bb') from dual;

3、initcap(c) 将字符串中单词的第一个字母转换为大写，其它则转换为小写
例如：select initcap('what is this') from dual;

4、nls_initcap(c) 返回指定字符串，并将字符串中第一个字母变大写，其它字母变小写
例如：select nls_initcap('中华minzhu') from dual;
它还具有一个参数：nlsparam用来指定排序规则，可以忽略，默认状态该参数为当前session的排序规则。

(三).字符型函数返回数字值(character functions returning number values)
本类函数支持所有的数据类型
1、ascii(c) 与chr函数的用途刚刚相反，本函数返回指定字符在当前字符集下对应的数值。
例如：select ascii('_') from dual;

(四).日期函数(datetime functions)
本类函数中，除months_between返回数值外，其它都将返回日期。
1、current_timestamp([n]) 返回当前session所在时区的日期和时间。n表示毫秒级的精度，不大于6
例如：select current_timestamp(3) from dual;

2、localtimestamp([n]) 与上同，返回当前session所在时区的日期和时间。n表示毫秒级的精度，不大于6
例如：select localtimestamp(3) from dual;

3、systimestamp([n]) 与上同，返回当前数据库所在时区的日期和时间，n表示毫秒级的精度，>0 and <6
例如：select systimestamp(4) from dual;

4、dbtimezone 返回数据库的当前时区
例如：select dbtimezone from dual;

5、sessiontimezone 返回当前session所在时区
例如：select sessiontimezone from dual;

6、extract(key from date) key=(year,month,day,hour,minute,second) 从指定时间提到指定日期列
例如：select extract(year from sysdate) from dual;

7、to_timestamp(c1[,fmt]) 将指定字符按指定格式转换为timestamp格式。
例如：select to_timestamp('2007-8-22', 'yyyy-mm-dd hh:mi:ss') from dual;

(五).转换函数(conversion functions)
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

1、bin_to_num(n1,n2...n) 将一组位向量转换为等价的十进制形式。
例如：select bin_to_num(1,1,0) from dual;

2、cast(c as newtype) 将指定字串转换为指定类型，基本只对字符类型有效，比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型，此处就不作酹述。
例如：select cast('1101' as number(5)) from dual;

3、chartorowid(c) 将字符串转换为rowid类型
例如：select chartorowid('a003d1abbefaabsaa0') from dual;

4、rowidtochar(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。
例如：select rowidtochar(rowid) from dual;

5、to_multi_byte(c) 将指定字符转换为全角并返回char类型字串
例如：select to_multi_byte('abc abc　中华') from dual;

6、to_single_byte(c) 将指定字符转换为半角并返回char类型字串
例如：select to_single_byte('ａｂｃ　ａｂｃ中华') from dual;

(六).其它辅助函数(miscellaneous single-row functions)
1、coalesce(n1,n2,....n) 返回序列中的第一个非空值
例如：select coalesce(null,5,6,null,9) from dual;

2、dump(exp[,fmt[,start[,length]]])
dump是个功能非常强悍的函数，对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法，不对其功能做深入分析。

如上所示，dump拥有不少参数。其本质是以指定格式，返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式：8||10||16||17，分别表示8进制，10进制，16进制和单字符，默认为10进制。start参数表示开始位置，length表示以,分隔的字串数。
例如：select dump('abcdefg',17,2,4) from dual;

3、empty_blob,empty_clob 这两个函数都是返回空lob类型，通常被用于insert和update等语句以初始化lob列，或者将其置为空。empty表示lob已经被初始化，只不过还没有用来存储数据。

4、nls_charset_name(n) 返回指定数值对应的字符集名称。
例如：select nls_charset_name(1) from dual;

5、nls_charset_id(c) 返回指定字符对应的字符集id。
例如：select nls_charset_id('us7ascii') from dual;

6、nls_charset_decl_len(n1,n2) 返回一个nchar值的声明宽度(以字符为单位).n1是该值以字节为单位的长度，n2是该值的字符集id
例如：select nls_charset_decl_len(100, nls_charset_id('us7ascii')) from dual;

7、sys_extract_utc(timestamp) 返回标准通用时间即格林威治时间。
例如：select sys_extract_utc(current_timestamp) from dual;

8、sys_typeid(object_type) 返回对象类型对应的id。
例如：这个这个，没有建立过自定义对象，咋做示例？

9、uid 返回一个唯一标识当前数据库用户的整数。
例如：select uid from dual;

10、user 返回当前session用户
例如：select user from dual;

11、userenv(c) 该函数用来返回当前session的信息，据oracle文档的说明，userenv是为了保持向下兼容的遗留函数。oracle公司推荐你使用sys_context函数调用userenv命名空间来获取相关信息，所以大家了解下就行了。
例如：select userenv('language') from dual;

12、vsize(c) 返回c的字节数。
例如：select vsize('abc中华') from dual;

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复