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

感于总有些网友提出一些非常基础的问题,比如有没有实现某某功能的函数啊,某某函数是做什么用的啊,格式是什么等等,同时也感受到自己对oracle函数认识的不足,于是集中月余时间专注于oracle函数,小有心得不敢私藏,发之与诸公共享。

本文并不准备介绍全部的oracle函数,当前情势下,俺也还没这个时间,需要学习的东西太多了,要把多数时间花在学习经常能用上的技术方面:),所以如果是准备深入了解所有oracle函数的朋友,还是去关注:Oracle SQL Reference官方文档更靠谱一些。

本文更多将会介绍三思在日常中经常会用到的,或者虽然很少用到,但是感觉挺有意思的一些函数。分二类介绍,分别是: 著名函数篇 -经常用到的函数

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

注: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?

? 如果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有些相似。但需要注

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4