Oracle中常用函数总结

1.模糊查询like

%表示零或多个字符
_表示一个字符
对于特殊符号可以使用ESCAPE标识符来查找
select * from emp where ename like '%*_%'escape'*';
上面的escape表示*后面的字符不被当作特殊字符处理，就是普通的'_'符。

-------------------------------------------下面才是正题-----------------------------------------------------

2.字符函数的使用

Upper 将字符串转为大写
	select Upper('aabbcc') from dual;
	select * from emp where ename=UPPER('smith');
	
	Lower 将字符串转为小写
	select Lower('AABBCC') from dual;
	select * from emp where ename=LOWER('SMITH');
	
	initcap 返回字符串并将首字母转为大写
	select initcap(ename) from emp;
	
	Concat 将后一个字符串连接到前一个字符串的尾部
	select concat('a','b') from dual;
	select 'a' || 'b' from dual;
	
	Substr 返回截取的字符串
	Substr(字符串,截取开始位置,截取长度)
	substr('Hello World',0,1) --返回结果为 'H'  *从字符串第一个字符开始截取长度为1的字符串
	substr('Hello World',1,1) --返回结果为 'H'  *0和1都是表示截取的开始位置为第一个字符
	substr('Hello World',2,4) --返回结果为 'ello'
	substr('Hello World',-3,3) --返回结果为 'rld' *用负数则从右边开始数第几个位置，截取仍然是从左往右截取
	测试：
	select substr('Hello World',-3,3) value from dual;
	
	length 返回字符串的长度
	select length(ename) from emp;
	
	replace 替换字段中指定的字符串
	replace(原字段，“原字段旧内容“,“原字段新内容“,)
	select replace(ename,'a','A') from emp;
	
	instr 在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。 
	instr(sourceString,destString,start,appearPosition) 
	其中sourceString代表源字符串； 
	destString代表要从源字符串中查找的子串； 
	start代表查找的开始位置，这个参数可选的，默认为1； 
	appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1 
	如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。 
	返回值为：查找到的字符串的位置。 
	select instr('Hello World','or') from dual; --返回结果:8 indexOf
	
	Lpad 
	lpad( string, padded_length, [ pad_string ] )
	string
　　准备被填充的字符串；
　　padded_length
　　填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
　　pad_string
　　填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
	lpad('Smith',10,'*')  --返回结果:*****Smith
	
	Rpad
	rpad(string,padded_length,[pad_string])
	string
　　被填充的字符串
　　padded_length
　　字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
　　pad_string
　　是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。
	Rpad('Smith',10,'*')  --返回结果为 Smith*****
	
	Trim 过滤掉首尾空格
	select trim(' Mr Smith ') from dual;--返回结果:Mr Smith

3.数值函数

Round
	将参数n按照n2指定的小数位进行四舍五入.不指定n2时默认n2为0,即近似到个位.如果第2个参数为负数时将对小数点左边的数作四舍五入,回到左面的|n|+1位.
	select round(345.678,2) from dual; --四舍五入到百分位(小数点后第2位)
	select round(412,-2) from dual; --返回结果:400
	select round(412.313,-2) from dual; --返回结果:400
	
	Mod(n,n2)
	返回参数n除以参数n2时的余数.
	select mod(10,2) from dual; -- 返回结果:0
	
	TRUNC(n,n2)	函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
	第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推。 
	TRUNC（89.985，2）=89.98
　　TRUNC（89.985）=89
　　TRUNC（89.985，-1）=80

4.日期函数

MONTHS_BETWEEN(DATE1,DATE2) 即MONTHS_BETWEEN(日期1，日期2)	函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数.
	否则，返回数值带小数，以每天1/31月来计算月中剩余天数。如果日期1比日期2小 ，返回值为负数。
	例：months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.29','yyyy.mm.dd')) 
    返回  12
	注：两个参数均为同样月份的29号，所以返回一整数。
	months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.12.24','yyyy.mm.dd'))
    返回   13.16129
	注：两个参数表示的日期不是同一天，所以返回带小数的值。
	
	add_months(x,y)或者add_months(times,months)函数
	这个函数用于计算在时间x之上机上Y个月后的时间值，要是Y的值为负数的话就是在这个时间点之间的时间值（这个时间-Y个月）。
	如 select add_months(sysdate,-6) from dual; 
	该查询的结果是当前时间半年前的时间
	select add_months(sysdate,6) from dual; 
	该查询的结果是当前时间半年后的时间
	
	NEXT_DAY(date,char) 
	date参数为日期型， 
	char:为1~7或Monday/Mon~Sunday/  1-星期天 2-星期一 依次类推
	select next_day(sysdate,'星期日') from dual;  --返回结果：2016.12.4 16:01:52
	select next_day(sysdate,1) from dual;  --和上面相同结果
	
	LAST_DAY 函数返回指定日期对应月份的最后一天。
	select last_day(to_date('2016.11.29','yyyy.mm.dd')) from dual;
	返回结果：2016.11.30
	select last_day(to_date('2017.2.2','yyyy.mm.dd')) from dual;
	返回结果：2017.2.28

5.转换函数

to_char(type, text)
	type:可以为timestamp、int、float、numeric格式
	text：转换的模板。如：'yyyy-mm-dd hh24:mi:ss'
	
	to_date(type, text)
	用法同上相反
	select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual; 
	注：很多Java程序员也许会直接的采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。
	原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。
	
	to_number 将字符串转换为数字数据类型
	select to_number('12')+to_number('13') from dual;
	返回结果:15

6.通用函数

sign(n)
	取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

	NVL(expr1,expr2)
	含义是：如果oracle第一个参数为空(null)那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
	注：Oracle会把''转化为null。但是'' 不等于' '。
	
	nvl2(expr1,expr2,expr3)
	如果 expr1 不是 null 值，则 nvl 函数返回 expr2 ，否则就返回 expr3 。参数可以返回任何数据类型的值，但是 expr2 和 expr3 不能是 LONG 型的数据类型。
	说明：若 expr2 和 expr3 的数据类型不同：
   	 (1)若 expr2 是字符数据，则Oracle数据库在比较之前就会把 expr3 转换成 expr2 的数据类型除非 expr3 是null。在 这种情况下，隐式数据转换是不必要的。Oracle数据库返回 VARCHAR2 数据类型到 expr2 的字符集。
    	(2) 若 expr2 是数值型，则Oracle数据库决定哪个参数具有最高数值优先级，并把另一个参数的数据类型隐式转换成这种数据类型，并返回这种数据类型的数据。
	
	NULLIF（表达式1，表达式2）
	如果表达式1和表达式2相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果。
	注意：表达式1和表达式2应该是相同数据类型或能隐含转换成相同数据类型，表达式1不能用字符null,但''不报错。
	
	COALESCE(表达式1,表达式2,...,表达式n)
	n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。
	注意：所有表达式必须为同一类型或者能转换成同一类型。
	
	case 表达式和java中的switch 类似
	select empno,ename,sal,case deptno 
									when 10 then '财务部'
									when 20 then '开发部'
									when 30 then '行政部'
							else '未知部门'
							end dept_name
	from emp;	

	DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
	表示如果value等于if1时，DECODE函数的结果返then1,...,如果不等于任何一个if值，则返回else。
	可以用函数或表达式来替代value，if，then，else从而作出一些更有用的比较。