SQLServer和Oracle常用函数对比 数学函数
1.绝对值 S:selectabs(-1)value O:selectabs(-1)valuefromdual
2.取整(大) S:selectceiling(-1.001)value O:selectceil(-1.001)valuefromdual
3.取整（小） S:selectfloor(-1.001)value O:selectfloor(-1.001)valuefromdual
4.取整（截取） S:selectcast(-1.002asint)value O:selecttrunc(-1.002)valuefromdual
5.四舍五入 S:selectround(1.23456,4)value1.23460 O:selectround(1.23456,4)valuefromdual1.2346
6.e为底的幂 S:selectExp(1)value2.7182818284590451 O:selectExp(1)valuefromdual2.71828182
7.取e为底的对数 S:selectlog(2.7182818284590451)value1 O:selectln(2.7182818284590451)valuefromdual;1 8.取10为底对数 S:selectlog10(10)value1 O:selectlog(10,10)valuefromdual;1
9.取平方 S:selectSQUARE(4)value16 O:selectpower(4,2)valuefromdual16
10.取平方根 S:selectSQRT(4)value2 O:selectSQRT(4)valuefromdual2
11.求任意数为底的幂 S:selectpower(3,4)value81 O:selectpower(3,4)valuefromdual81
12.取随机数 S:selectrand()value O:selectsys.dbms_random.value(0,1)valuefromdual;
13.取符号 S:selectsign(-8)value-1 O:selectsign(-8)valuefromdual-1
14.圆周率 S:SELECTPI()value3.1415926535897931 O:不知道
15.sin,cos,tan参数都以弧度为单位 例如：selectsin(PI()/2)value得到1（SQLServer）
16.Asin,Acos,Atan,Atan2返回弧度
17.弧度角度互换(SQLServer，Oracle不知道) DEGREES：弧度-〉角度 RADIANS：角度-〉弧度数值间比较
18.求集合最大值 S:selectmax(value)valuefrom (select1value union select-2value union select4value union select3value)a O:selectgreatest(1,-2,4,3)valuefromdual
19.求集合最小值 S:selectmin(value)valuefrom (select1value union select-2value union select4value union select3value)a O:selectleast(1,-2,4,3)valuefromdual
20.如何处理null值(F2中的null以10代替) S:selectF1,IsNull(F2,10)valuefromTbl O:selectF1,nvl(F2,10)valuefromTbl
21.求字符序号 S:selectascii('a')value O:selectascii('a')valuefromdual
22.从序号求字符 S:selectchar(97)value O:selectchr(97)valuefromdual
23.连接 S:select'11'+'22'+'33'value O:selectCONCAT('11','22') 33valuefromdual
23.子串位置--返回3 S:selectCHARINDEX('s','sdsq',2)value O:selectINSTR('sdsq','s',2)valuefromdual 23.模糊子串的位置--返回2,参数去掉中间%则返回7 S:selectpatindex('%d%q%','sdsfasdqe')value O:oracle没发现，但是instr可以通过第四个参数控制出现次数 selectINSTR('sdsfasdqe','sd',1,2)valuefromdual返回6
24.求子串 S:selectsubstring('abcd',2,2)value O:selectsubstr('abcd',2,2)valuefromdual
25.子串代替返回aijklmnef S:SELECTSTUFF('abcdef',2,3,'ijklmn')value O:SELECTReplace
('abcdef','bcd','ijklmn')valuefromdual
26.子串全部替换 S:没发现 O:selectTranslate('fasdbfasegas','fa','我')valuefromdual
27.长度 S:len,datalength O:length
1.绝对值 S:selectabs(-1)value O:selectabs(-1)valuefromdual
2.取整(大) S:selectceiling(-1.001)value O:selectceil(-1.001)valuefromdual
3.取整（小） S:selectfloor(-1.001)value O:selectfloor(-1.001)valuefromdual
4.取整（截取） S:selectcast(-1.002asint)value O:selecttrunc(-1.002)valuefromdual
5.四舍五入 S:selectround(1.23456,4)value1.23460 O:selectround(1.23456,4)valuefromdual1.2346
6.e为底的幂 S:selectExp(1)value2.7182818284590451 O:selectExp(1)valuefromdual2.71828182
7.取e为底的对数 S:selectlog(2.7182818284590451)value1 O:selectln(2.7182818284590451)valuefromdual;1 8.取10为底对数 S:selectlog10(10)value1 O:selectlog(10,10)valuefromdual;1
9.取平方 S:selectSQUARE(4)value16 O:selectpower(4,2)valuefromdual16
10.取平方根 S:selectSQRT(4)value2 O:selectSQRT(4)valuefromdual2
11.求任意数为底的幂 S:selectpower(3,4)value81 O:selectpower(3,4)valuefromdual81
12.取随机数 S:selectrand()value O:selectsys.dbms_random.value(0,1)valuefromdual;
13.取符号 S:selectsign(-8)value-1 O:selectsign(-8)valuefromdual-1
14.圆周率 S:SELECTPI()value3.1415926535897931 O:不知道
15.sin,cos,tan参数都以弧度为单位 例如：selectsin(PI()/2)value得到1（SQLServer）
16.Asin,Acos,Atan,Atan2返回弧度
17.弧度角度互换(SQLServer，Oracle不知道) DEGREES：弧度-〉角度 RADIANS：角度-〉弧度数值间比较
18.求集合最大值 S:selectmax(value)valuefrom (select1value union select-2value union select4value union select3value)a O:selectgreatest(1,-2,4,3)valuefromdual
19.求集合最小值 S:selectmin(value)valuefrom (select1value union select-2value union select4value union select3value)a O:selectleast(1,-2,4,3)valuefromdual
20.如何处理null值(F2中的null以10代替) S:selectF1,IsNull(F2,10)valuefromTbl O:selectF1,nvl(F2,10)valuefromTbl
21.求字符序号 S:selectascii('a')value O:selectascii('a')valuefromdual
22.从序号求字符 S:selectchar(97)value O:selectchr(97)valuefromdual
23.连接 S:select'11'+'22'+'33'value O:selectCONCAT('11','22') 33valuefromdual
23.子串位置--返回3 S:selectCHARINDEX('s','sdsq',2)value O:selectINSTR('sdsq','s',2)valuefromdual 23.模糊子串的位置--返回2,参数去掉中间%则返回7 S:selectpatindex('%d%q%','sdsfasdqe')value O:oracle没发现，但是instr可以通过第四个参数控制出现次数 selectINSTR('sdsfasdqe','sd',1,2)valuefromdual返回6
24.求子串 S:selectsubstring('abcd',2,2)value O:selectsubstr('abcd',2,2)valuefromdual
25.子串代替返回aijklmnef S:SELECTSTUFF('abcdef',2,3,'ijklmn')value O:SELECTReplace
('abcdef','bcd','ijklmn')valuefromdual
26.子串全部替换 S:没发现 O:selectTranslate('fasdbfasegas','fa','我')valuefromdual
27.长度 S:len,datalength O:length
