Oracle认证综合辅导:常用函数对比与总结
发布时间:2012/9/5 22:45:06 来源:城市网学院 编辑:admin
我们知道,不同的数据库所支持的函数也是有所不同的,有些函数是通用的,有些是某些数据库所特有的。本文我们主要对SQL Server,Oracle,DB2数据库上常用函数进行了对比与总结,希望能够对您有所帮助,接下来就让我们一起来了解一下这部分内容吧。
1.字符函数:
1.SELECT empno, ename, deptno FROM emp
2.WHERE upper(ename) =upper( 'blake‘);
1.SQL SERVER:select 'Good'+'String' ORACLE:select concat('Good’,‘String’) from dual;
2. DB2:select concat(‘Good’,‘String’) from sysibm.sysdummy1;
1.SELECT ename, CONCAT (ename, job), LENGTH(ename), INSTR(ename, 'A‘)
2.FROM emp WHERE
3.SUBSTR(job,1,5) = 'SALES’;
1.SQL SERVER: SELECT ename, (ename+job), LEN(ename), PATINDEX(‘%A%’,ename)
2. FROM emp WHERE SUBSTRING(job,1,5) = 'SALES‘;
2.日期函数:
1. SQL SERVER: select getdate() ORACLE: select sysdate from dual;
2. DB2:SELECT current date FROM sysibm.sysdummy1 ;
1. SELECT ename,(sysdate-hiredate)/7 weeks
2.FROM emp WHERE deptno=10;
1. SELECT ename,datediff(day,hiredate,GETDATE())/7 weeks FROM emp WHERE deptno=10;
1. SELECT DATEPART(month, GETDATE()) AS 'Month Number' SELECT DATEPART(day, GETDATE()) AS 'Month Number'
1.select datediff(month,'1991-6-12’,‘1992-6-21’) as a select datediff(day,'1991-6-12‘,'1992-6-21’) as a
1.SELECT ename,(sysdate-hiredate)/7 weeks FROM emp WHERE deptno=10;
1. SELECT ename,(days(current date)- days(date(hiredate)))/7 weeks FROM emp WHERE deptno=10;
3.转换函数:
1. SELECT ename, convert(char(10), hiredate) HIREDATE
2. FROM emp;
1.SELECT ename, cast(hiredate as char(10)) HIREDATE
2. FROM emp;
1.SELECT ename, TO_CHAR(hiredate, 'fmDD Month YYYY‘) HIREDATE
2. FROM emp;
1.select char(current date) from sysibm.sysdummy1; select char(current time) from sysibm.sysdummy1;