日期：2011-05-02 15:22:00 来源：本站整理
SQLServer和Oracle常用函数比较[MSSQL防范]
本文“SQLServer和Oracle常用函数比较[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
数学函数?
1.绝对值?
S:select?abs(-1)?value?
O:select?abs(-1)?value?from?dual?
2.取整(大)?
S:select?ceiling(-1.001)?value?
O:select?ceil(-1.001)?value?from?dual?
3.取整（小）?
S:select?floor(-1.001)?value?
O:select?floor(-1.001)?value?from?dual?
4.取整（截取）?
S:select?cast(-1.002?as?int)?value?
O:select?trunc(-1.002)?value?from?dual?
5.四舍五入?
S:select?round(1.23456,4)?value?1.23460?
O:select?round(1.23456,4)?value?from?dual?1.2346?
6.e为底的幂?
S:select?Exp(1)?value?2.7182818284590451?
O:select?Exp(1)?value?from?dual?2.71828182?
7.取e为底的对数?
S:select?log(2.7182818284590451)?value?1?
O:select?ln(2.7182818284590451)?value?from?dual;?1?
8.取10为底对数?
S:select?log10(10)?value?1?
O:select?log(10,10)?value?from?dual;?1?
9.取平方?
S:select?SQUARE(4)?value?16?
O:select?power(4,2)?value?from?dual?16?
10.取平方根?
S:select?SQRT(4)?value?2?
O:select?SQRT(4)?value?from?dual?2?
11.求肆意数为底的幂?
S:select?power(3,4)?value?81?
O:select?power(3,4)?value?from?dual?81?
12.取随机数?
S:select?rand()?value?
O:select?sys.dbms_random.value(0,1)?value?from?dual;?
13.取标记?
S:select?sign(-8)?value?-1?
O:select?sign(-8)?value?from?dual?-1?
14.圆周率?
S:SELECT?PI()?value?3.1415926535897931?
O:不知道?
15.sin,cos,tan?参数都以弧度为单位?
比方:select?sin(PI()/2)?value?得到1（SQLServer）?
16.Asin,Acos,Atan,Atan2?返回弧度?
17.弧度角度交换(SQLServer,Oracle不知道)?
DEGREES:弧度-〉角度?
RADIANS:角度-〉弧度?
数值间对比?
18.?求调集最大值?
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?
19.?求调集最小值?
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?
20.若何处理null值(F2中的null以10替换)?
S:select?F1,IsNull(F2,10)?value?from?Tbl?
O:select?F1,nvl(F2,10)?value?from?Tbl?
21.求字符序号?
S:select?ascii('a')?value?
O:select?ascii('a')?value?from?dual?
22.从序号求字符?
S:select?char(97)?value?
O:select?chr(97)?value?from?dual?
23.衔接?
S:select?'11'+'22'+'33'?value?
O:select?CONCAT('11','22') 33?value?from?dual?
23.子串位置?--返回3?
S:select?CHARINDEX('s','sdsq',2)?value?
O:select?INSTR('sdsq','s',2)?value?from?dual?
23.模糊子串的位置?--返回2,参数去掉中间%则返回7?
S:select?patindex('%d%q%','sdsfasdqe')?value?
O:oracle没发现,但是instr可以通过第四个参数掌握呈现次数?
select?INSTR('sdsfasdqe','sd',1,2)?value?from?dual?返回6?
24.求子串?
S:select?substring('abcd',2,2)?value?
O:select?substr('abcd',2,2)?value?from?dual?
25.子串替换?返回aijklmnef?
S:SELECT?STUFF('abcdef',?2,?3,?'ijklmn')?value?
O:SELECT?Replace('abcdef',?'bcd',?'ijklmn')?value?from?dual?
26.子串全部替换?
S:没发现?
O:select?Translate('fasdbfasegas','fa','我'?)?value?from?dual?
27.长度?
S:len,datalength?
O:length?
28.大小写转换?lower,upper?
29.单词首字母大写?
S:没发现?
O:select?INITCAP('abcd?dsaf?df')?value?from?dual?
30.左补空格（LPAD的第一个参数为空格则同space函数）?
S:select?space(10)+'abcd'?value?
O:select?LPAD('abcd',14)?value?from?dual?
31.右补空格（RPAD的第一个参数为空格则同space函数）?
S:select?'abcd'+space(10)?value?
O:select?RPAD('abcd',14)?value?from?dual?
32.删除空格?
S:ltrim,rtrim?
O:ltrim,rtrim,trim?
33.?反复字符串?
S:select?REPLICATE('abcd',2)?value?
O:没发现?
34.发音类似性对比(这两个单词返回值一样,发音相同)?
S:SELECT?SOUNDEX?('Smith'),?SOUNDEX?('Smythe')?
O:SELECT?SOUNDEX?('Smith'),?SOUNDEX?('Smythe')?from?dual?
SQLServer顶用SELECT?DIFFERENCE('Smithers',?'Smythers')?对比soundex的差?
返回0-4,4为同音,1最高?
日期函数?
35.系统时间?
S:select?getdate()?value?
O:select?sysdate?value?from?dual?
36.前后几日?
直接与整数相加减?
37.求日期?
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?
38.求时间?
S:select?convert(char(8),getdate(),108)?value?
O:select?to_char(sysdate,'hh24:mm:ss')?value?from?dual?
39.取日期时间的其他部份?
S:DATEPART?和?DATENAME?函数?（第一个参数决意）?
O:to_char函数?第二个参数决意?
参数---------------------------------下表需求增补?
year?yy,?yyyy?
quarter?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?dual?
41.本星期的某一天（比方星期日）?
S:不知道?
O:SELECT?Next_day(sysdate,7)?vaule?FROM?DUAL;?
42.字符串转时间?
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;?
43.求两日期某一部份的差（比方秒）?
S:select?datediff(ss,getdate(),getdate()+12.3)?value?
O:直接用两个日期相减（比方d1-d2=12.3）?
SELECT?(d1-d2)*24*60*60?vaule?FROM?DUAL;?
44.按照差值求新的日期（比方分钟）?
S:select?dateadd(mi,8,getdate())?value?
O:SELECT?sysdate+8/60/24?vaule?FROM?DUAL;?
45.求差别时区时间?
S:不知道?
O:SELECT?New_time(sysdate,'ydt','gmt'?)?vaule?FROM?DUAL;?
-----时区参数,北京在东8区应当是Ydt-------?
AST?ADT?大西洋尺度时间?
BST?BDT?白令海尺度时间?
CST?CDT?中部尺度时间?
EST?EDT?东部尺度时间?
GMT?格林尼治尺度时间?
HST?HDT?阿拉斯加?夏威夷尺度时间?
MST?MDT?山区尺度时间?
NST?纽芬兰尺度时间?
PST?PDT?安定洋尺度时间?
YST?YDT?YUKON尺度时间
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“SQLServer和Oracle常用函数比较”更多相关内容
中查找“SQLServer和Oracle常用函数比较”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论