SQL Server 2012/2016/2017 新增函数

1. /**************************************************************
   
2. SQL Server 2012 新增的函数
   
3. ***************************************************************/
4.  
5. -- CONCAT ( string_value1, string_value2 [, string_valueN ] ) #字符串相连
   
6. SELECT CONCAT('A','BB','CCC','DDDD')
   
7. --结果：ABBCCCDDDD
8.  
9. -- PARSE ( string_value AS data_type [ USING culture ] ) #转换为所请求的数据类型的表达式的结果
   
10. SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
    
11. SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result;
12.  
13. SET LANGUAGE 'English';
    
14. SELECT PARSE('12/16/2010' AS datetime2) AS Result;
15.  
16. /*结果：
    
17. 2010-12-13 00:00:00.0000000
    
18. 345.98
    
19. 2010-12-16 00:00:00.0000000
    
20. */
21.  
22. -- TRY_CAST 、TRY_CONVERT、TRY_PARSE (TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型)
    
23. SELECT TRY_CAST('test' AS float),TRY_CAST(5 AS VARCHAR)
    
24. SELECT TRY_CONVERT(float,'test'),TRY_CONVERT(VARCHAR,5)
    
25. SELECT TRY_PARSE('test' AS float),TRY_PARSE('01/01/2011' AS datetime2)
    
26. /*结果：
    
27. NULL 5
    
28. NULL 5
    
29. NULL 2011-01-01 00:00:00.0000000
    
30. */
31.  
32. -- CHOOSE ( index, val_1, val_2 [, val_n ] ) #返回指定索引处的项 (即返回第几个值)
    
33. SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
    
34. --结果：Developer
35.  
36. -- IIF ( boolean_expression, true_value, false_value )
    
37. SELECT IIF ( 10 > 5, 'TRUE', 'FALSE' ) AS Result;
    
38. SELECT (CASE WHEN 10 > 5 THEN 'TRUE' ELSE 'FALSE' END) AS Result;
    
39. --结果：TRUE
40.  
41. -- 排名函数！
    
42. SELECT *
    
43. ,ROW_NUMBER ( ) OVER (PARTITION BY MyName ORDER BY Num) AS 'ROW_NUMBER' --按顺序排名
    
44. ,DENSE_RANK ( ) OVER (PARTITION BY MyName ORDER BY Num) AS 'DENSE_RANK' --同排名的后面排名连续
    
45. ,RANK ( ) OVER (PARTITION BY MyName ORDER BY Num) AS 'RANK' --同排名的后面排名不连续
    
46. ,NTILE (2) OVER (PARTITION BY MyName ORDER BY Num) AS 'NTILE' --按总数分两组，顺序排名
    
47. FROM (VALUES('AA',55),('AA',30.5),('BB',55),('BB',99),('BB',0),('BB',55))AS T(MyName,Num)
    
48. ORDER BY MyName,Num
    
49. /*
    
50. MyName Num ROW_NUMBER DENSE_RANK RANK NTILE
    
51. ------ ----- ---------- ---------- ------ -----
    
52. AA 30.5 1 1 1 1
    
53. AA 55.0 2 2 2 2
    
54. BB 0.0 1 1 1 1
    
55. BB 55.0 2 2 2 1
    
56. BB 55.0 3 2 2 2
    
57. BB 99.0 4 3 4 2
    
58. */
59.  
60. -- 分析函数！
    
61. SELECT *
    
62. ,CUME_DIST( )OVER (PARTITION BY MyName ORDER BY Num) AS 'CUME_DIST' --相对（最大值）位置
    
63. ,PERCENT_RANK( )OVER (PARTITION BY MyName ORDER BY Num) AS 'PERCENT_RANK' --相对排名,排名分数参考 CUME_DIST
    
64. ,FIRST_VALUE (MyName)OVER ( ORDER BY Num ASC) AS 'FIRST_VALUE' --Num 最低的是哪个MyName
    
65. ,LAST_VALUE (MyName)OVER ( ORDER BY Num ASC) AS 'LAST_VALUE' --Num 排序选底部的那个MyName
    
66. ,LAG (Num,1,0)OVER (ORDER BY Num ASC) AS 'LAG' --上/下一行(或多行)的值移到下/上一行(或多行)，方便对比
    
67. ,LEAD (Num,1,0)OVER (ORDER BY Num ASC) AS 'LEAD' --与LAG一样，排序相反
    
68. ,PERCENTILE_CONT(0.5)WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY MyName) AS 'PERCENTILE_CONT' --连续分布计算百分位数
    
69. ,PERCENTILE_DISC(0.5)WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY MyName) AS 'PERCENTILE_DISC' --离散分布计算百分位数
    
70. FROM (VALUES('AA',55),('AA',30.5),('BB',55),('BB',99),('BB',0),('BB',55))AS T(MyName,Num)
    
71. ORDER BY Num ASC
72.  
73. /*
    
74. MyName Num CUME_DIST PERCENT_RANK FIRST_VALUE LAST_VALUE LAG LEAD PERCENTILE_CONT PERCENTILE_DISC
    
75. ------ ----- --------- ------------ ----------- ---------- ----- ----- --------------- ---------------
    
76. BB 0.0 0.25 0 BB BB 0.0 30.5 55 55.0
    
77. AA 30.5 0.5 0 BB AA 0.0 55.0 42.75 30.5
    
78. AA 55.0 1 1 BB BB 30.5 55.0 42.75 30.5
    
79. BB 55.0 0.75 0.33333 BB BB 55.0 55.0 55 55.0
    
80. BB 55.0 0.75 0.33333 BB BB 55.0 99.0 55 55.0
    
81. BB 99.0 1 1 BB BB 55.0 0.0 55 55.0
    
82. */
83.  
84. /**************************************************************
    
85. SQL Server 2014 新增的函数
    
86. ***************************************************************/
87.  
88. --貌似没有什么
89.  
90. /**************************************************************
    
91. SQL Server 2016 新增的函数
    
92. ***************************************************************/
93.  
94. -- STRING_SPLIT ( string , separator ) #字符分割
    
95. SELECT value FROM STRING_SPLIT('A,B,C',',')
    
96. /*结果：
    
97. value
    
98. -----
    
99. A
    
100. B
     
101. C
     
102. */
103.  
104. -- STRING_ESCAPE( text , type ) #特殊字符转成带有转义字符的文本（type只支持json）
     
105. SELECT STRING_ESCAPE('\ / \\ " ', 'json') AS escapedText;
     
106. --结果：\\ \/ \\\\ \"
107.  
108. -- DATEDIFF_BIG ( datepart , startdate , enddate ) #日期之间的计数
     
109. SELECT DATEDIFF(day, '2005-12-12', '2017-10-10'); --以前版本
     
110. SELECT DATEDIFF_BIG(day, '2005-12-12', '2017-10-10');
     
111. SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
     
112. /*结果：
     
113. 4320
     
114. 4320
     
115. 1
     
116. */
117.  
118. -- inputdate AT TIME ZONE timezone #时区时间
     
119. SELECT * FROM sys.time_zone_info -- 时区及名称参考
     
120. SELECT CONVERT(DATETIME,'2017-10-10') AT TIME ZONE 'Pacific Standard Time'
     
121. SELECT CONVERT(DATETIME,'2017-10-10') AT TIME ZONE 'China Standard Time'
     
122. SELECT CONVERT(datetime2(0), '2017-10-10T01:01:00', 126) AT TIME ZONE 'Pacific Standard Time';
     
123. SELECT CONVERT(datetime2(0), '2017-10-10T01:01:00', 126) AT TIME ZONE 'China Standard Time';
     
124. /*结果：
     
125. 2017-10-10 00:00:00.000 -07:00
     
126. 2017-10-10 00:00:00.000 +08:00
     
127. 2017-10-10 01:01:00 -07:00
     
128. 2017-10-10 01:01:00 +08:00
     
129. */
130.  
131. -- COMPRESS ( expression ) # GZIP算法压缩为varbinary(max)
     
132. DECLARE @COM varbinary(max)
     
133. SELECT @COM = COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}')
     
134. SELECT @COM
     
135. --结果：0x1F8B08000000000004002DCC410A80300C44D17F94D2B51B85A2780E2FE042A414AAD4BA12EFEE……（略）
136.  
137. -- DECOMPRESS ( expression )#解压缩
     
138. SELECT CAST(DECOMPRESS(@COM) AS NVARCHAR(MAX))
     
139. --结果：{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}
140.  
141. -- SESSION_CONTEXT(N'key') #获取指定的键的值
     
142. EXEC sp_set_session_context 'user_id', 4; --设置键值
     
143. SELECT SESSION_CONTEXT(N'user_id');
     
144. --结果：4
145.  
146. -- ISJSON ( expression ) #测试字符串是否包含有效JSON
     
147. DECLARE @param1 NVARCHAR(MAX)
     
148. DECLARE @param2 NVARCHAR(MAX)
     
149. SET @param1 = N' "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 '
     
150. SET @param2 = N'[{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }]'
     
151. SELECT ISJSON(@param1) as P1, ISJSON(@param2) as P2
     
152. GO
     
153. /*结果：
     
154. P1 P2
     
155. -- --
     
156. 0 1
     
157. */
158.  
159. -- JSON_VALUE ( expression , path ) #从 JSON 字符串中提取值
     
160. DECLARE @param NVARCHAR(MAX)
     
161. SET @param = N'{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }'
     
162. SELECT JSON_VALUE(@param,'$.id') as P1,JSON_VALUE(@param,'$.info.name')as P2
     
163. GO
     
164. /*结果：
     
165. P1 P2
     
166. -- ----
     
167. 2 John
     
168. */
169.  
170. -- JSON_QUERY ( expression [ , path ] ) #从 JSON 字符串中提取对象或数组
     
171. DECLARE @param NVARCHAR(MAX)
     
172. SET @param = N'{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }'
     
173. SELECT JSON_QUERY(@param,'$.info')
     
174. GO
     
175. --结果：{ "name": "John", "surname": "Smith" }
176.  
177. -- JSON_MODIFY ( expression , path , newValue ) #更新的 JSON 字符串中属性的值并返回更新的 JSON 字符串
     
178. DECLARE @param NVARCHAR(MAX)
     
179. SET @param = N'{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }'
     
180. SELECT JSON_MODIFY(@param,'$.info.surname','newValue')
     
181. GO
     
182. --结果：{ "id" : 2,"info": { "name": "John", "surname": "newValue" }, "age": 25 }
183.  
184. /**************************************************************
     
185. SQL Server 2017 新增的函数
     
186. ***************************************************************/
187.  
188. -- CONCAT_WS ( separator, argument1, argument1 [, argumentN]… ) #按第一个分隔符连接后面的字符
     
189. SELECT CONCAT_WS( ' - ', 1, 'kk', '12dd')
     
190. --结果：1 - kk - 12dd
191.  
192. -- TRANSLATE ( inputString, characters, translations) #整体对应替换
     
193. SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
     
194. SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')');
     
195. SELECT TRANSLATE('2*[3+4]/[7-2]', '[2', '');
     
196. /*结果：
     
197. 2*(3+4)/(7-2)
     
198. 2*(3+4)/(7-2)
     
199. 1*63+4]/67-1]
     
200. */
201.  
202. -- TRIM ( [ characters FROM ] string ) #删除字符串左右空格字符
     
203. SELECT TRIM( ' test ') AS Result,LTRIM(RTRIM(' test '))
204.  
205. -- STRING_AGG ( expression, separator ) #同列字符相连成一行
     
206. SELECT STRING_AGG (MyName, CHAR(13)) FROM (VALUES('AAAA'),('BBBBB'),('CCCCCC') )AS T(MyName)
     
207. SELECT STRING_AGG (MyName,',') FROM (VALUES('AAAA'),('BBBBB'),('CCCCCC') )AS T(MyName)
     
208. SELECT STRING_AGG (MyName,',') WITHIN GROUP(ORDER BY id DESC ) FROM (VALUES(1,'AAAA'),(1,'BBBBB'),(2,'CCCCCC'))AS T(id,MyName)
     
209. /*结果：
     
210. AAAA BBBBB CCCCCC
     
211. AAAA,BBBBB,CCCCCC
     
212. CCCCCC,BBBBB,AAAA
     
213. */