oracle+110个常用函数.docx

《oracle+110个常用函数.docx》由会员分享，可在线阅读，更多相关《oracle+110个常用函数.docx（39页珍藏版）》请在第一文库网上搜索。

1、1. ASCII返回与指定的字符对应的十进制数；SQL select ascii(A) Azascii(a) a,ascii(O) zerozasdi( ) space from dual; A A ZERO SPACE65 97 48 322. CHR给出整数,返回对应的字符；SQL select chr(54740) zhaozchr(65) chr65 from dual;ZH C赵A3. CONCAT连接两个字符串；SQL select concat(010-z88888888) 23 高乾竞电话 from dual;高乾竞电话OlO-88888888 转 234. INITCAP返回

2、字符串并将字符串的第一个字母变为大写；SQL select initcap(smith) upp from dual;UPPSmith5.INSTR(ClzC2zIrJ)在一个字符串中搜索指定的字符,返回发现指定的字符的位置；Cl被搜索的字符串C2希望搜索的字符串I搜索的开始位置,默认为1J出现的位置,默认为1SQL select instr(oracle traningzrazl,2) instring from dual; INSTRING96 . LENGTH 返回字符串的长度；SQL select name,length(name),addi;length(addr)zsalzleng

3、th(to_char(sal) from gao.nchar_tst;NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)高乾竞3北京市海锭区6 9999.99 77 .L0WER返回字符串,并将所有的字符小写SQL select Iower(AaBbCcDd)AaBbCcDd from dual; AABBCCDDaabbccdd8 .UPPER返回字符串,并将所有的字符大写SQL select Upper(AaBbCcDd) upper from dual;UPPERAABBCCDD9 .RPAD和LPAD(粘贴字符)RPA

4、D在列的右边粘贴字符LPAD在列的左边粘贴字符SQL select lpad(rpad(gaoz10z*),17z*)from dual;LPAD(RPAD(GAOzl*gao*不够字符则用*来填满10.LTRlM 和 RTRlMLTRIM删除左边出现的字符串RTRIM删除右边出现的字符串SQL select ltrim(rtrim( gao qian jing , ), ) from dual;LTRIM(RTRIM(gao qian jing1 l.SUBSTR(stringzstartzcount)取子字符串,从start开始,取count个SQL select substr(13088

5、888888z3z8) from dual;SUBSTR(0888888812.REPLACE(stringzslzs2)string希望被替换的字符或变量Sl被替换的字符串S2要替换的字符串SQL select replace(he love youzhe,i) from dual;REPLACE(Hi love you13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQL create table tablel(xm varchar(8);SQL insert into tablel values(weather);SQL insert into tablel values(we

6、ther);SQL insert into tablel values(gao);SQL select xm from tablel where soundex(xm)=soundex(weather);XMweatherwether 14.TRIM(s from string)LEADING剪掉前面的字符TRAILING剪掉后面的字符如果不指定,默认为空格符15.ABS返回指定值的绝对值SQL select abs(100)zabs(-100) from dual;ABS(IOO) ABS(-100)100 10016.ACOS给出反余弦的值SQL select acos(-l) from

7、dual;ACOS(-1)3.141592717.ASIN给出反正弦的值SQL select asin(0.5) from dual;ASIN(0.5).5235987818.ATAN返回一个数字的反正切值SQL select atan(l) from dual;ATAN(I).7853981619.CEIL返回大于或等于给出数字的最小整数SQL select ceil(3.1415927) from dual;CEIL(3.1415927)420.COS返回一个给定数字的余弦SQL select cos(-3.1415927) from dual;COS(-3.1415927)-121.CO

8、SH返回一个数字反余弦值SQL select cosh(20) from dual;COSH(20)24258259822 .EXP返回一个数字e的n次方根SQL select exp(2)zexp(l) from dual;EXP(2) EXP(I)7.3890561 2.718281823 .FLOOR对给定的数字取整数SQL select floor(2345.67) from dual;FLOOR(2345.67)234524 . LN返回一个数字的对数值SQL select ln(l),ln(2)Jn(2.7182818) from dual;LN(I) LN(2) LN(2.718

9、2818)0 .69314718 .9999999925.LOG(nlzn2)返回一个以nl为底n2的对数SQL select log(2,l)Jog(2z4) from dual;LOG(2zl) LOG(2z4)0 226.MOD(nlzn2)返回一个nl除以n2的余数SQL select mod(10,3)zmod(3z3)zmod(2z3) from dual;MOD(10,3) MOD(3z3) MOD(2,3)10 227 .POWER返回nl的n2次方根SQL select power(2z10)zpower(3z3) from dual;POWER(210) POWER(3z3

10、)1024 2728 .ROUND 和 TRUNC按照指定的精度进行舍入SQL select round(55.5)zround(-55.4)ztrunc(55.5)ztrunc(-55.5) from dual;ROND(55.5) ROND(-55.4) TRNC(55.5) TRNC(-55.5)56 -55 55 -5529 .SIGN取数字n的符号,大于0返回1,小于0返回等于。返回0 SQL select sign(123),sign(-100)zsign(0) from dual; SIGN(123) SIGN(-100) SIGN(O)1-1030.SIN返回一个数字的正弦值S

11、QL select sin(l.57079) from dual;SIN(1.57079)1 31.SIGH返回双曲正弦的值SQL select sin(20),sinh(20) from dual;SIN(20) SINH(2O).91294525 24258259832.SQRT返回数字n的根SQL select sqrt(64)zsqrt(10) from dual;SQRT(64) SQRT(IO)8 3.162277733.TAN返回数字的正切值SQL select tan(20)ztan(10) from dual;TAN(20) TAN(IO)2.2371609 .6483608

12、334.TANH返回数字n的双曲正切值SQL select tanh(20),tan(20) from dual;TANH(20) TAN(20)1 2.237160935.TRUNC按照指定的精度截取一个数SQL select trunc( 124.1666z-2) truncl,trunc(124.16666,2) from dual;TRUNCl TRNC(124.16666z2)100 124.1636.ADD_MONTHS增加或减去月份SQL select to_char(add_months(to_date(199912zyyyymm)z2),yyyymm) from dual;T

13、O_CHA200002SQL select to_char(add_months(to_date(199912zyyyymm)z-2),yyyymm) from dual;TO_CHA19991037.LAST DAY返回日期的最后一天SQL select to-char(sysdatezyyyy.mm.dd)zto-char(sysdate)+l,yyyy.mm.dd) from dual; TO_CHAR(SY TO_CHAR(S 2004.05.09 2004.05.10SQL select last_day(sysdate) from dual;LAST_DAY(S 31-5 月-04

14、38.MONTHS_BETWEEN(date2zdatel) 给出date2-datel的月份SQL select months_between(19-12 月-1999,19-3 月-1999) mon_between from dual;MON_BETWEEN 9SQLselectmonths_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd) mon_betw from dual;MON_BETW-6039.NEW_TIME(date,this,that)给出在this时区=Othe门时区的日期和时间SQL select to_char(sysdatezyyyy.mm.dd hh24:mi:ss) bj_timezto_char(new_time2 (SySdate,PDTGMT),yyyy.mm.dd hh24:mi:ss) los_angles from dual;BJ_TIME LOS_ANGLES2004.05.09 11:05:32 2004.05.09 18:05:324O.NEXT_DAY(datezday)给出日期date和星期X之后计算下一个星期的日期SQL select next_day(18-5 月-2001,星期五)net-day from dual;NEX