sql sever与Oracle的异同点
原来一直接触Oracle现在一家客户要求数据库由Oracle切换成sql sever，才对sql数据库有了一些基本的了解
打算给百度学习的新人整理些常用的函数
1.oracle中定义时间的格式可以用:to_char(sysdate,'yyyy-mm-dd')得到的是2020-03-04
而sqlsever用convert(varchar(10),getdate(),120),得到的是2020-03-04
其中120是日期格式的数字代码，varchar(10)是取前10位
2.sql sever 没有dual这个虚拟表，可以直接写查询
Oracle写 select 3-2 from dual --返回1
sql直接写 select 3-2 --返回1
3.sql与plsql的用法
4.查找指定字符串中指定子字符串出现的起始位置。
sql用CharIndex()语法为
select charindex('世','你好世界') --返回3 --先指定字符后字符源
Oracle用 instr()语法为instr('你好世界','世') --返回3 --先字符源后字符窜
--可以指定该字符窜第几次出现的次数instr('你好世界世界'，'世'，2)
5.Oracle的substr(),
select substr('helloword'，1,5) from daul --返回 hello
select substr('helloword'，5) from daul --返回 hello --默认从第一位开始截取
sql sever用substring() 都是截取字符窜长度的函数
select substring('helloword'，1,5) --返回 hello --必须三个参数
6.sql 用Stuff()：用于删除指定长度的字符，并在删除的位置插入新的字符/值。该方法有四个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要删除字符的起始位置。
参数3：用于指定要删除字符的长度。
参数4：用于指定在删除的位置插入的新的字符串/值。
select stuff('123abc456',4,3,'ABC') -- 返回 123ABC456
select stuff('123abc456',1,3,'') -- 返回 abc456，用空字符串替代
sql的Replace()：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。该方法有三个参数：
参数1：用于指定要操作的字符串，即被匹配的字符串。
参数2：用于指定要进行匹配的字符串。
参数3：用于指定用作替换存在的匹配项的字符串。
--把 abc 替换为 xxx
select replace('123abc456','abc','xxx') -- 返回 123xxx456
-- 用空字符串替换匹配项
select replace('123abc456','abc','') -- 返回 123456
Oracle用replace的
replace（'将要更改的字符串','被替换掉的字符串','替换字符串'）
例：select replace ('111222333444','222','888') from dual;
输出为 '111888333444'
7. sql用
Len()：用于返回指定文本的值的长度。前导空格计算在内，尾随空格不计算在内。该方法有一个参数：
参数1：用于指定要操作的文本或字符串。
select len('123') -- 返回 3
select len('字符串') -- 返回 3
select len(' 123') -- 返回 4
select len('123 ') -- 返回 3
oracle 用length（）
select length('123') from dual --返回 3
select length(' 123') from dual -- 返回 4
select length('123 ') from dual -- 返回 4
8.
Difference()：用于返回一个整数值，指示两个字符表达式的 SOUNDEX 值之间的差异。（即两个字符串的相似度）那么什么是 SOUNDEX 值呢？先记着，下面轮到它了。
返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。该方法有两个参数：
参数1：用于指定要进行比对的第一个字符串 SOUNDEX 值 。
参数2：用于指定要进行比对的第二个字符串 SOUNDEX 值。
select difference('action','demo') -- 返回 2
select difference('123456','整数') -- 返回 4
9.sql 中
Lower()：用于返回指定英文字符串的小写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为小写形式的字符串。
select lower('ABC') -- 返回 abc
select lower('123') -- 返回 123
Upper()：用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为大写形式的字符串。
select upper('abc') -- 返回 ABC
select upper('123') -- 返回 123
Oracle同样用upper（） 与lower()
10.--去空字符窜Ltrim()：用于返回删除前导空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行删除前导空格操作的字符串。
select ltrim(' 123') -- 返回 123
select ltrim(' 好多空格') -- 返回 好多空格
select len(' 123') -- 返回 11
select len(ltrim(' 123')) -- 返回 3
Rtrim()：用于返回截断尾随空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行截断尾随空格操作的字符串。
select rtrim('123 ') -- 返回 123
select len(rtrim('123 ')) -- 返回 3
Oracle中用trim（）相当于sql的Rtrim(ltrim())：
11.sql
Isdate()：用于判断指定的字符串表达式是否为日期。返回 1，表示指定字符串表达式是日期。返回 0，表示指定字符串表达式不是日期。该方法有一个参数：
参数1：用于指定要操作的字符串表达式。
select isdate('10/30/2017') -- 月/日/年 返回 1
select isdate('30/10/2017') -- 日/月/年 返回 0
select isdate('2017/10/30') -- 年/月/日 返回 1
select isdate('02/29/2017') --用于判断闰年,由于2017不是闰年,所以没有29 返回 0
Oracle中好像没有单独判断是否是日期的方法
12.
Datename()：返回指定日期的指定日期的部分的字符串。该方法有两个参数：
参数1：指定返回指定日期的指定部分的字符串。可以为下图中的日期部分或者缩写。
参数2：指定要操作的日期字符串。
select datename(year,'2017-01-01 10:10:10') -- 返回 2017
select datename(yyyy,'2017-01-01 10:10:10') -- 返回 2017
select datename(month,'2017-01-01 10:10:10') -- 返回 01
select datename(mm,'2017-01-01 10:10:10') -- 返回 01
select datename(quarter,'2017-05-02 10:10:10') -- 表示一年的第几个季度 返回 2
select datename(weekday,'2017-02-02 10:10:10') -- 表示一周的星期几 返回 星期四
select datename(week,'2017-02-02 10:10:10') -- 表示一年的第几周 返回 5
select datename(dayofyear,'2017-02-02 10:10:10') -- 表示一年的第几天 返回 33
select datename(hh,'2017-01-01 10:30:25') -- 返回 10
Datepart()：返回指定日期的指定日期的部分的整数。Datepart() 方法和 Datename() 的方法使用方式类似，只不过 Datename() 方法返回的是一个字符串，而 Datepart() 方法返回的是一个整数数值。该方法有两个参数：
参数1：指定返回指定日期的指定部分的字符串。可以为上图中的日期部分或者缩写。
参数2：指定要操作的日期字符串。
select datepart(year,'2017-01-01 10:10:10') -- 返回 2017 select datepart(yyyy,'2017-01-01 10:10:10') -- 返回 2017
Select datepart（year,getdate()+100） --返回2120
13.
sql 的 Coalesce()：返回所有参数中的第一个非空（not null）表达式。该方法最多可以有 n 个参数，但是最少要有两个参数。
SELECT COALESCE('abc','') -- 返回 abc
SELECT COALESCE(NULL,'') -- 返回 空字符串
SELECT COALESCE(null,null,'123','abc') -- 返回 123
PS：这个函数是返回第一个非空的值，所以参数里面必须最少有一个非空的值。
Oracle的nvl(a,b)
sql 的 ISNull()：判断指定的表达式一是否为空（null），如果为空则返回表达式二的值，否则返回表达式一的值，类似于C#中的三元运算符。该方法有两个参数：
参数1：指定要操作的表达式一。
参数2：指定要操作的表达式二。
select isnull('','123') -- 返回 空字符串
select isnull(null,'123') -- 返回 123
select isnull('123','abc') -- 返回 123
select isnull(null,null) -- 返回 null
------------------------------------
数学函数：
1.绝对值
Sql Server: SELECT abs(-1) ---返回1
Oracle: SELECT abs(-1) value from dual ---返回1
2.取整(大)
Sql Server: SELECT ceiling(-1.001) --返回-1
Oracle: SELECT ceil(-1.001) value from dual --返回-1
3.取整（小）
Sql Server: SELECT floor(-1.001)
Oracle: SELECT floor(-1.001) value from dual
4.取整（截取）
Sql Server: SELECT cast(-1.002 as int) ---返回-1
SELECT cast(1.99 as int) ---返回1
Oracle: SELECT trunc(-1.002) value from dual
5.四舍五入
Sql Server: SELECT round(1.23456789,4) --返回1.2346000
Oracle: SELECT round(1.23456,4) value from dual --返回1.2346
6.e为底的幂
Sql Server: SELECT Exp(1) value 2.7182818284590451
Oracle: SELECT Exp(1) value from dual 2.71828182
7.取e为底的对数
Sql Server: SELECT log(2.7182818284590451) value 1
Oracle: SELECT ln(2.7182818284590451) value from dual; 1
8.取10为底对数
Sql Server: SELECT log10(10) value 1
Oracle: SELECT log(10,10) value from dual; 1
9.取平方
Sql Server: SELECT SQUARE(4) value 16
Oracle: SELECT power(4,2) value from dual 16
10.取平方根
Sql Server: SELECT SQRT(4) value 2
Oracle: SELECT SQRT(4) value from dual 2
11.求任意数为底的幂
Sql Server: SELECT power(3,4) value 81
Oracle: SELECT power(3,4) value from dual 81
12.取随机数
Sql Server: SELECT rand() value
Oracle: SELECT sys.dbms_random.value(0,1) value from dual;
13.取符号
Sql Server: SELECT sign(-8) value -1
Oracle: SELECT sign(-8) value from dual -1
14.圆周率
Sql Server: SELECT PI() value 3.1415926535897931
Oracle: 不知道
15.sin,cos,tan 参数都以弧度为单位
例如：SELECT sin(PI()/2) value 得到1（SQLServer）
16.Asin,Acos,Atan,Atan2 返回弧度
17.弧度角度互换(SQLServer，Oracle不知道)
DEGREES：弧度-〉角度
RADIANS：角度-〉弧度
数值间比较：
18. 求集合最大值
Sql Server: SELECT max(value) value from
(SELECT 1 value
union
SELECT -2 value
union
SELECT 4 value
union
SELECT 3 value)a
Oracle: SELECT greatest(1,-2,4,3) value from dual
19. 求集合最小值
Sql Server: SELECT min(value) value from
(SELECT 1 value
union
SELECT -2 value
union
SELECT 4 value
union
SELECT 3 value)a
Oracle: SELECT least(1,-2,4,3) value from dual
20.如何处理null值(F2中的null以10代替)
Sql Server: SELECT F1,IsNull(F2,10) value from Tbl
Oracle: SELECT F1,nvl(F2,10) value from Tbl
21.求字符序号
Sql Server: SELECT ascii('a') value
Oracle: SELECT ascii('a') value from dual
22.从序号求字符
Sql Server: SELECT char(97) value
Oracle: SELECT chr(97) value from dual
23.连接
Sql Server: SELECT '11'+'22'+'33' value
Oracle: SELECT CONCAT('11','22') 33 value from dual
23.子串位置 --返回3
Sql Server: SELECT CHARINDEX('s','sdsq',2) value
Oracle: SELECT INSTR('sdsq','s',2) value from dual
23.模糊子串的位置 --返回2,参数去掉中间%则返回7
Sql Server: SELECT patindex('%d%q%','sdsfasdqe') value
Oracle: oracle没发现，但是instr可以通过第四个参数控制出现次数
SELECT INSTR('sdsfasdqe','sd',1,2) value from dual 返回6
24.求子串
Sql Server: SELECT substring('abcd',2,2) value
Oracle: SELECT substr('abcd',2,2) value from dual
25.子串代替 返回aijklmnef
Sql Server: SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
Oracle: SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
26.子串全部替换
Sql Server: 没发现
Oracle: SELECT Translate('fasdbfasegas','fa','我' ) value from dual
27.长度
Sql Server: len,datalength
Oracle: length
28.大小写转换 lower,upper
29.单词首字母大写
Sql Server: 没发现
Oracle: SELECT INITCAP('abcd dsaf df') value from dual
30.左补空格（LPAD的第一个参数为空格则同space函数）
Sql Server: SELECT space(10)+'abcd' value
Oracle: SELECT LPAD('abcd',14) value from dual
31.右补空格（RPAD的第一个参数为空格则同space函数）
Sql Server: SELECT 'abcd'+space(10) value
Oracle: SELECT RPAD('abcd',14) value from dual
32.删除空格
Sql Server: ltrim,rtrim
Oracle: ltrim,rtrim,trim
33. 重复字符串
Sql Server: SELECT REPLICATE('abcd',2) value
Oracle: 没发现
34.发音相似性比较(这两个单词返回值一样，发音相同)
Sql Server: SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
Oracle: SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差
返回0-4，4为同音，1最高
日期函数：
35.系统时间
Sql Server: SELECT getdate() value
Oracle: SELECT sysdate value from dual
36.前后几日
直接与整数相加减
37.求日期
Sql Server: SELECT convert(char(10),getdate(),20) value
Oracle: SELECT trunc(sysdate) value from dual
SELECT to_char(sysdate,'yyyy-mm-dd') value from dual
38.求时间
Sql Server: SELECT convert(char(8),getdate(),108) value
Oracle: SELECT to_char(sysdate,'hh24:mm:ss') value from dual
39.取日期时间的其他部分
Sql Server: DATEPART 和 DATENAME 函数 （第一个参数决定）
Oracle: 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.当月最后一天
Sql Server: 不知道
Oracle: SELECT LAST_DAY(sysdate) value from dual
41.本星期的某一天（比如星期日）
Sql Server: 不知道
Oracle: SELECT Next_day(sysdate,7) vaule FROM DUAL;
42.字符串转时间
Sql Server: 可以直接转或者SELECT cast('2004-09-08'as datetime) value
Oracle: SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;
43.求两日期某一部分的差（比如秒）
Sql Server: SELECT datediff(ss,getdate(),getdate()+12.3) value
Oracle: 直接用两个日期相减（比如d1-d2=12.3）
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44.根据差值求新的日期（比如分钟）
Sql Server: SELECT dateadd(mi,8,getdate()) value
Oracle: SELECT sysdate+8/60/24 vaule FROM DUAL;
45.求不同时区时间
Sql Server: 不知道
Oracle: 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标准时间
二、相同
===============================
函数
Left()： Right()：
