oracle11g函数

1.字符函数

ascii('字符串') \ chr(ASCII码)

返回第一个字母的ASCII码\函数返回十进制表示的字符 $ `" W. \2 g2 y3 X% G( v

select ascii('Android') big_A,ascii('android') small_a from dual; //65 97

select chr(65) big_A,chr(97) small_a from dual; //A a

concat(string1,string2)

将string2连续到string1的后面 

select concat('oracle','SQL') name from dual; //oracleSQL

initcap(string)

将每个单词的第一个字母大写其他字母小写返回 1 p3 P6 E. P2 G' |0 M3 c+ O

select initcap('anroid,java,c#') ceasar from dual; //Anroid,Java,C# 1 p3 P6 E. P2 G' |0 M3 c+

upper(string)/lower(string)

select upper('hi') from dual; //HI

select lower('HI') from dual; //hi

instr(string1,string2,index_postion,num) 

返回string2在string1中第num次出现的位置,搜索从string1的第index_postion个字符开始,无则返回0

(从左到右第三个位置开始查找Mississippi中第三次出现i的位置)
select instr('Mississippi','i',3,3) from dual;//11

(从右到左第三个位置开始查找Mississippi中第三次出现i的位置)

select instr('Mississippi','i',-3,3) from dual;//2

length(string)

返回string的长度

select length('android java') ergo from dual;//12

lower(string)

返回string的小写状态

select lower('ANDROID JAVA') ergo from dual;//android java

Lpad(string,length,append)

Rpad(string,length,append)

将append补至string前满足最大长度为length;若最大长度<string则从左向右截取字符串

将append补至string后满足最大长度为length;若最大长度<string则从左向右截取字符串

select Lpad('tech', 8, '0') padded from dual;//0000tech

select Rpad('tech', 8, '0') padded from dual;//tech0000

select Lpad('tech', 2) padded from dual;//te

select Rpad('tech', 2) padded from dual;//ch

Ltrim(string,pattern)

Rtrim(string,pattern)

从最左边的字符开始,使其第一个字符不在pattern中,如果没有pattern,那么string就不会改变 + g3 g- [, j6 i( y6 v1 t8 `) H

从最右边的字符开始,使其第一个字符不在pattern中,如果没有pattern,那么string就不会改变 + g3 g- [, j6 i( y6 v1 t8 `) H
select Ltrim('Mississippi','Mis')from dual;//ippi

select Ltrim('MTississippi','Mis')from dual;//Tississippi

select Rtrim('Mississippi','Mis')from dual;//Mississipp

select Rtrim('MTississippi','pis')from dual;//MT

replace(string1,string2,string3)

用string3代替出现在string1中的string2 

select replace('uptown','up','down') from dual;//downtown

substr(string1,index,end)

select substr('Message',1,4) from dual;//Mess

translate(string,str1,str2)

将string中与str1相同的字符以str2代替 ! n0 D" h. L* }6 X' \7 f/ 

select translate('fumble','uf','ar') test from dual;//ramble

trim(pattern,string)

从字符串的头部,尾部或两端截断特定字符

select trim(' ' from ' AB  C D EF  ') from dual;//AB  C D EF

2.数学函数

abs(数字字符/数字)     exp(数字字符/数字)

floor(数字字符/数字)   mod(数字1，数字2)

power(数字,次方)   round(数字1，四舍五入保留位数)

sqrt(数字)   

返回绝对值

返回e的n次幂,e=2.71828183 , R& K8 N; y2 J; X. p  l

返回小于等于其的最小整数 l

返回数字1%数字2

返回数字的次方

返回四舍五入后数字

返回数字的平方根

select abs('-23') from dual; //23

select floor(-16.35) from dual;//-17

select mod(18,4) from dual; //2 

select power(2,3) from dual;  //8

select round(12345.54621,2) from dual;//12345.55 

select sqrt(8) from dual; //2.82842712474619009760337744841939615714

trunc(日期或数字) 

/**************日期********************/
1 select to_char(trunc(sysdate),'yyyy-MM-dd') from dual          //2014-03-18   返回当前年月日

   select to_char(trunc(sysdate,'dd'),'yyyy-MM-dd') from dual   //2014-03-18   返回当前年月日
2.select to_char(trunc(sysdate,'mm'),'yyyy-MM-dd') from dual //2014-03-01   返回当月第一天.
3.select to_char(trunc(sysdate,'yy'),'yyyy-MM-dd') from dual //   2014-01-01   返回当年第一天

   select to_char(trunc(sysdate,'yyyy'),'yyyy-MM-dd') from dual //   2014-01-01   返回当年第一天
4.select to_char(trunc(sysdate,'d'),'yyyy-MM-dd') from dual //2014-03-16 返回当前星期的第一天
5.select to_char(trunc(sysdate,'hh'),'yyyy-MM-dd HH:mi:ss') from dual //2014-03-16 09:00:00
/***************数字********************/
6.  select trunc(123.458) from dual //123
7.  select trunc(123.458,0) from dual //123

8.  select trunc(123.458,1) from dual //123.4

9.  select trunc(123.458,-1) from dual //120

10 select trunc(123.458,-4) from dual //0

11.select trunc(123.458,4) from dual //123.458

12.select trunc(123) from dual //123

13.select trunc(123,1) from dual //123
14.select trunc(123,-1) from dual //120