5.5.1 Oracle和SQL Server的常用函数对比
1.数学函数
- ①绝对值
- S:select abs(-1) value
- O:select abs(-1) value from dual
- ②取整(大)
- S:select ceiling(-001) value
- O:select ceil(-001) value from dual
- ③取整(小)
- S:select floor(-001) value
- O:select floor(-001) value from dual
- ④取整(截取)
- S:select cast(-002 as int) value
- O:select trunc(-002) value from dual
- ⑤四舍五入
- S:select round(23456,4) value 23460
- O:select round(23456,4) value from dual 2346
- ⑥e为底的幂
- S:select Exp(1) value
- O:select Exp(1) value from dual
- ⑦取e为底的对数
- S:select log(7182818284590451) value
- O:select ln(7182818284590451) value from dual;
- ⑧取10为底对数
- S:select log10(10) value
- O:select log(10,10) value from dual;
- ⑨取平方
- S:select SQUARE(4) value
- O:select power(4,2) value from dual
- ⑩取平方根
- S:select SQRT(4) value
- O:select SQRT(4) value from dual
- 求任意数为底的幂
- S:select power(3,4) value
- O:select power(3,4) value from dual
- 取随机数
- S:select rand() value
- O:select sys.dbms_random.value(0,1) value from dual;
- 取符号
- S:select sign(-8) value -1
- O:select sign(-8) value from dual -1
2.数值比较
3.字符串函数
- ①求集合最大值
- S:select max(value) value from
- (select 1 value union
- select -2 value union
- select 4 value union
- select 3 value)a
- O:select greatest(1,-2,4,3) value from dual
- ②求集合最小值
- S:select min(value) value from
- (select 1 value union
- select -2 value union
- select 4 value union
- select 3 value)a
- O:select least(1,-2,4,3) value from dual
- ③如何处理null值(F2字段中的null以10代替)
- S:select F1,IsNull(F2,10) value from Tbl
- O:select F1,nvl(F2,10) value from Tbl
- ①求字符序号
- S:select ascii('a') value
- O:select ascii('a') value from dual
- ②从序号求字符
- S:select char(97) value
- O:select chr(97) value from dual
- ③连接
- S:select '11'+'22'+'33' value
- O:select CONCAT('11','22')||33 value from dual
- ④子串位置--返回3
- S:select CHARINDEX('s','sdsq',2) value
- O:select INSTR('sdsq','s',2) value from dual
- ⑤模糊子串的位置--返回2,参数去掉中间%则返回7
- S:select patindex('%d%q%','sdsfasdqe') value
- O:oracle没发现,但是instr可以通过第四个参数控制出现次数
- select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6
- ⑥求子串
- S:select substring('abcd',2,2) value
- O:select substr('abcd',2,2) value from dual
- ⑦子串代替--返回aijklmnef
- S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
- O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
- ⑧左补空格(LPAD的第1个参数为空格则同space函数)
- S:select space(10)+'abcd' value
- O:select LPAD('abcd',14) value from dual
- ⑨右补空格(RPAD的第1个参数为空格则同space函数)
- S:select 'abcd'+space(10) value
- O:select RPAD('abcd',14) value from dual
- ⑩发音相似性比较(这2个单词返回值一样,发音相同)
- S:select soundex ('Smith'), soundex ('Smythe')
- O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
- SQL Server中用SELECT DIFFERENCE('Smithers', 'Smythers')
比较soundex的差返回0~4,4为同音,1最高
4.日期函数
- ①系统时间
- S:select getdate() value
- O:select sysdate value from dual
- ②求日期
- S:select convert(char(10),getdate(),20) value
- O:select trunc(sysdate) value from dual
- select to_char(sysdate,'yyyy-mm-dd') value from dual
- ③求时间
- S:select convert(char(8),getdate(),108) value
- O:select to_char(sysdate,'hh24:mm:ss') value from dual
- ④当月最后一天
- S:不知道
- O:select LAST_DAY(sysdate) value from dual
- ⑤本星期的某一天(比如星期日)
- S:不知道
- O:SELECT Next_day(sysdate,7) vaule FROM DUAL;
- ⑥字符串转时间
- S:select cast('2004-09-08'as datetime) value
- O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss')
vaule FROM DUAL;- ⑦求2日期某一部分的差(比如秒)
- S:select datediff(ss,getdate(),getdate()+3) value
- O:直接用两个日期相减(比如d1-d2=3)
- SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
- ⑧根据差值求新的日期(比如分钟)
- S:select dateadd(mi,8,getdate()) value
- O:SELECT sysdate+8/60/24 vaule FROM DUAL;
联系客服