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

(一)本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思

的一些函数。分二类介绍，分别是：

著名函数篇－经常用到的函数

非著名函数篇－即虽然很少用到，但某些情况下却很实用

注：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, A TAN, and A TAN2支持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,V ARCHAR2,NCHAR,NV ARCHAR2,CLOB,NCLOB类型

例如：SELECT LOWER('WhaT is tHis') FROM DUAL;

2、UPPER(c) 将指定字符串内字符变为大写，支持CHAR,V ARCHAR2,NCHAR,NV ARCHAR2,CLOB,NCLOB类型

例如：SELECT UPPER('WhaT is tHis') FROM DUAL;

3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点：

如果nλ

如果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。