SQLServer和Oracle常用函數(shù)對(duì)比寶藏

1、數(shù)學(xué)函數(shù)(S 表示sqlserver O表示oracle1.絕對(duì)值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為底的冪O:select Exp(1 value from dual 2.718281827.取e為底的對(duì)數(shù)8.取10為底對(duì)數(shù)S:select log10(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:s

3、elect SQRT(4 value 2O:select SQRT(4 value from dual 211.求任意數(shù)為底的冪S:select power(3,4 value 81O:select power(3,4 value from dual 8112.取隨機(jī)數(shù)S:select rand( value13.取符號(hào)S:select sign(-8 value -1O:select sign(-8 value from dual -114.圓周率O:不知道15.sin,cos,tan 參數(shù)都以弧度為單位例如:select sin(PI(/2 value 得到1(SQLServer16.As

4、in,Acos,Atan,Atan2 返回弧度17.弧度角度互換(SQLServer,Oracle不知道 DEGREES:弧度-角度RADIANS:角度-弧度數(shù)值間比較18. 求集合最大值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 valueunionse

5、lect -2 valueunionselect 4 valueunionselect 3 valueaO:select least(1,-2,4,3 value from dual20.如何處理null值(F2中的null以10代替S:select F1,IsNull(F2,10 value from TblO:select F1,nvl(F2,10 value from Tbl21.求字符序號(hào)S:select ascii(a valueO:select ascii(a value from dual22.從序號(hào)求字符S:select char(97 valueO:select chr(97

6、 value from dual23.連接S:select 11+22+33 valueO:select CONCAT(11,2233 value from dual23.子串位置 -返回3S:select CHARINDEX(s,sdsq,2 valueO:select INSTR(sdsq,s,2 value from dual23.模糊子串的位置 -返回2,參數(shù)去掉中間%則返回7S:select patindex(%d%q%,sdsfasdqe valueO:oracle沒發(fā)現(xiàn),但是instr可以通過(guò)第四個(gè)參數(shù)控制出現(xiàn)次數(shù)select INSTR(sdsfasdqe,sd,1,2 val

7、ue 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, bcd, ijklmn value from dual26.子串全部替換S:沒發(fā)現(xiàn)O:select Translate(fasdbfasegas,fa,我 value from dual27.長(zhǎng)度S:len,datalengthO:lengt

8、h28.大小寫轉(zhuǎn)換 lower,upper29.單詞首字母大寫S:沒發(fā)現(xiàn)O:select INITCAP(abcd dsaf df value from dual30.左補(bǔ)空格(LPAD的第一個(gè)參數(shù)為空格則同space函數(shù) S:select space(10+abcd valueO:select LPAD(abcd,14 value from dual31.右補(bǔ)空格(RPAD的第一個(gè)參數(shù)為空格則同space函數(shù) S:select abcd+space(10 valueO:select RPAD(abcd,14 value from dual32.刪除空格S:ltrim,rtrimO:ltrim

9、,rtrim,trim33. 重復(fù)字符串S:select REPLICATE(abcd,2 valueO:沒發(fā)現(xiàn)34.發(fā)音相似性比較(這兩個(gè)單詞返回值一樣,發(fā)音相同S:SELECT SOUNDEX (Smith, SOUNDEX (SmytheO:SELECT SOUNDEX (Smith, SOUNDEX (Smythe from dualSQLServer中用SELECT DIFFERENCE(Smithers, Smythers 比較soundex的差返回0-4,4為同音,1最高日期函數(shù)35.系統(tǒng)時(shí)間S:select getdate( valueO:select sysdate valu

10、e from dual36.前后幾日直接與整數(shù)相加減37.求日期S:select convert(char(10,getdate(,20 valueO:select trunc(sysdate value from dualselect to_char(sysdate,yyyy-mm-dd value from dual38.求時(shí)間S:select convert(char(8,getdate(,108 valueO:select to_char(sysdate,hh24:mm:ss value from dual39.取日期時(shí)間的其他部分S:DATEPART 和 DATENAME 函數(shù)(第一

11、個(gè)參數(shù)決定O:to_char函數(shù)第二個(gè)參數(shù)決定參數(shù)-下表需要補(bǔ)充year yy, yyyyquarter qq, q (季度month mm, m (m O無(wú)效dayofyear dy, y (O表星期day dd, d (d O無(wú)效week wk, ww (wk O無(wú)效weekday dw (O不清楚Hour hh,hh12,hh24 (hh12,hh24 S無(wú)效minute mi, n (n O無(wú)效second ss, s (s O無(wú)效millisecond ms (O無(wú)效-40.當(dāng)月最后一天S:不知道O:select LAST_DAY(sysdate value from dual41.本星期的某一天(比如星期日S:不知道O:SELECT Next_day(sysdate,7 vaule FROM DUAL;42.字符串轉(zhuǎn)時(shí)間S:可以直接轉(zhuǎn)或者select cast(2004-09-08as 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:直接用兩個(gè)日期相減(比如d1-d2=12.3SELECT (d1-