SQLServer和Oracle常用函数对比宝藏

1、数学函数(S 表示sqlserver O表示oracle1.绝对值S:select abs(-1 valueO:select abs(-1 value from dual2.取整(大S:select ceiling(-1.001 valueO:select ceil(-1.001 value from dual3.取整(小S:select floor(-1.001 valueO:select floor(-1.001 value from dual4.取整(截取S:select cast(-1.002 as int valueO:select trunc(-1.002 value from d

2、ual5.四舍五入S:select round(1.23456,4 value 1.23460O:select round(1.23456,4 value from dual 1.23466.e为底的幂S:select Exp(1 value 2.7182818284590451O:select Exp(1 value from dual 2.718281827.取e为底的对数S:select log(2.7182818284590451 value 1O:select ln(2.7182818284590451 value from dual; 18.取10为底对数S:select log1

3、0(10 value 1O:select log(10,10 value from dual; 19.取平方S:select SQUARE(4 value 16O:select power(4,2 value from dual 1610.取平方根S:select SQRT(4 value 2O:select SQRT(4 value from dual 211.求任意数为底的幂S:select power(3,4 value 81O:select power(3,4 value from dual 8112.取随机数S:select rand( valueO:select sys.dbms_

4、random.value(0,1 value from dual;13.取符号S:select sign(-8 value -1O:select sign(-8 value from dual -114.圆周率S:SELECT PI( value 3.1415926535897931O:不知道15.sin,cos,tan 参数都以弧度为单位例如:select sin(PI(/2 value 得到1(SQLServer16.Asin,Acos,Atan,Atan2 返回弧度17.弧度角度互换(SQLServer,Oracle不知道 DEGREES:弧度-角度RADIANS:角度-弧度数值间比较1

5、8. 求集合最大值S:select max(value value from(select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 valueaO:select greatest(1,-2,4,3 value from dual19. 求集合最小值S:select min(value value from(select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 valueaO:select least(1,-2,4,3 value fro

6、m dual20.如何处理null值(F2中的null以10代替S:select F1,IsNull(F2,10 value from TblO:select F1,nvl(F2,10 value from Tbl21.求字符序号S:select ascii('a' valueO:select ascii('a' value from dual22.从序号求字符S:select char(97 valueO:select chr(97 value from dual23.连接S:select '11'+'22'+'33&#

7、39; valueO:select CONCAT('11','22'33 value from dual23.子串位置 -返回3S:select CHARINDEX('s','sdsq',2 valueO:select INSTR('sdsq','s',2 value from dual23.模糊子串的位置 -返回2,参数去掉中间%则返回7S:select patindex('%d%q%','sdsfasdqe' valueO:oracle没发现,但是instr可以通

8、过第四个参数控制出现次数select INSTR('sdsfasdqe','sd',1,2 value from dual 返回624.求子串S:select substring('abcd',2,2 valueO:select substr('abcd',2,2 value from dual25.子串代替返回aijklmnefS:SELECT STUFF('abcdef', 2, 3, 'ijklmn' valueO:SELECT Replace('abcdef', 'b

9、cd', 'ijklmn' value from dual26.子串全部替换S:没发现O:select Translate('fasdbfasegas','fa','我' value from dual27.长度S:len,datalengthO:length28.大小写转换 lower,upper29.单词首字母大写S:没发现O:select INITCAP('abcd dsaf df' value from dual30.左补空格(LPAD的第一个参数为空格则同space函数 S:select spac

10、e(10+'abcd' valueO:select LPAD('abcd',14 value from dual31.右补空格(RPAD的第一个参数为空格则同space函数 S:select 'abcd'+space(10 valueO:select RPAD('abcd',14 value from dual32.删除空格S:ltrim,rtrimO:ltrim,rtrim,trim33. 重复字符串S:select REPLICATE('abcd',2 valueO:没发现34.发音相似性比较(这两个单词返回值

11、一样,发音相同S:SELECT SOUNDEX ('Smith', SOUNDEX ('Smythe'O:SELECT SOUNDEX ('Smith', SOUNDEX ('Smythe' from dualSQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers' 比较soundex的差返回0-4,4为同音,1最高日期函数35.系统时间S:select getdate( valueO:select sysdate value from dual36.前

12、后几日直接与整数相加减37.求日期S:select convert(char(10,getdate(,20 valueO:select trunc(sysdate value from dualselect to_char(sysdate,'yyyy-mm-dd' value from dual38.求时间S:select convert(char(8,getdate(,108 valueO:select to_char(sysdate,'hh24:mm:ss' value from dual39.取日期时间的其他部分S:DATEPART 和 DATENAME

13、函数(第一个参数决定O:to_char函数第二个参数决定参数-下表需要补充year yy, yyyyquarter qq, q (季度month mm, m (m O无效dayofyear dy, y (O表星期day dd, d (d O无效week wk, ww (wk O无效weekday dw (O不清楚Hour hh,hh12,hh24 (hh12,hh24 S无效minute mi, n (n O无效second ss, s (s O无效millisecond ms (O无效-40.当月最后一天S:不知道O:select LAST_DAY(sysdate value from du

14、al41.本星期的某一天(比如星期日S:不知道O:SELECT Next_day(sysdate,7 vaule FROM DUAL;42.字符串转时间S:可以直接转或者select cast('2004-09-08'as datetime valueO:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss ' vaule FROM DUAL;43.求两日期某一部分的差(比如秒S:select datediff(ss,getdate(,getdate(+12.3 valueO:直接用两个日期相减(比如d1-d2=12