SQL Server2012新特性：新增内置函数

亦凉 2021-06-26 16:06 522阅读 0赞

标注为红色的是个人觉得比较有用的几个函数。

<table> 
 <thead> 
  <tr> 
   <th align="left">序号</th> 
   <th align="left">名称</th> 
   <th align="left">类型</th> 
   <th align="left">说明</th> 
   <th align="left">MSDN</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left"><font>Concat</font></td> 
   <td align="left">字符串类</td> 
   <td align="left">返回作为串联两个或更多字符串值的结果的字符串。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/concat-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left"><font>Format</font></td> 
   <td align="left">字符串类</td> 
   <td align="left">返回以指定的格式和可选的区域性格式化的值。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/format-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left"><font>CHOOSE</font></td> 
   <td align="left">逻辑类</td> 
   <td align="left">在 SQL Server 中从值列表返回指定索引处的项。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/logical-functions-choose-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left"><font>IIF</font></td> 
   <td align="left">逻辑类</td> 
   <td align="left">在 SQL Server 中，根据布尔表达式计算为 true 还是 false，返回其中一个值。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/logical-functions-iif-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">5</td> 
   <td align="left">DATEFROMPARTS</td> 
   <td align="left">日期类</td> 
   <td align="left">返回日期指定的年、 月和日的值。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datefromparts-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">6</td> 
   <td align="left">DATETIMEFROMPARTS</td> 
   <td align="left">日期类</td> 
   <td align="left">返回datetime值的指定的日期和时间。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datetimefromparts-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">7</td> 
   <td align="left">DATETIME2FROMPARTS</td> 
   <td align="left">日期类</td> 
   <td align="left">返回datetime2值的指定的日期和时间并使用指定的精度。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datetime2fromparts-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">8</td> 
   <td align="left">DATETIMEOFFSETFROMPARTS</td> 
   <td align="left">日期类</td> 
   <td align="left">返回datetimeoffset值的指定的日期和时间并使用指定的偏移量和精度。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datetimeoffsetfromparts-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">9</td> 
   <td align="left">TIMEFROMPARTS</td> 
   <td align="left">日期类</td> 
   <td align="left">返回时间值指定的时间并使用指定的精度。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/timefromparts-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">10</td> 
   <td align="left"><font>EOMONTH</font></td> 
   <td align="left">日期类</td> 
   <td align="left">返回包含指定日期的月份的最后一天（具有可选偏移量）。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/eomonth-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">11</td> 
   <td align="left">PARSE</td> 
   <td align="left">转换类</td> 
   <td align="left">返回 SQL Server 中转换为所请求的数据类型的表达式的结果。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/parse-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left">12</td> 
   <td align="left">TRY_PARSE</td> 
   <td align="left">转换类</td> 
   <td align="left">在 SQL Server 中，返回表达式的结果（已转换为请求的数据类型）；</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/try-parse-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left">如果强制转换失败，则返回 Null。 TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型。</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">13</td> 
   <td align="left">TRY_CONVERT</td> 
   <td align="left">转换类</td> 
   <td align="left">返回转换为指定数据类型的值（如果转换成功）；否则返回 Null。</td> 
   <td align="left"><a href="https://docs.microsoft.com/zh-cn/sql/t-sql/functions/try-convert-transact-sql" rel="nofollow">msdn</a></td> 
  </tr> 
 </tbody> 
</table>

SET NOCOUNT ON
    --1. CONCAT
    --concat 的好处：1. 可以跨数据类型; 2. 忽略 NULL
    SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25', NULL ) AS r1, 'Happy'+NULL AS r2;
    /* r1 r2 ------------------------------ ------ Happy Birthday 11/25 NULL */
     
    --2. FORMAT
    ----1. 可以根据区域得到不同的日期格式
    ----2. 可以自定义格式字符串（日期和数字）
    ----3. 可以根据区域得到不同的数据格式（如加 $ ）
    DECLARE @d DATETIME = '10/01/2011';  
    SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'  
          ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'  
          ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'  
          ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result';  
    /* US English Result Great Britain English Result German Result Simplified Chinese (PRC) Result 10/1/2011 01/10/2011 01.10.2011 2011/10/1 */ 
     
    SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'  
          ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'  
          ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'  
          ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';  
    /* US English Result Great Britain English Result German Result Chinese (Simplified PRC) Result Saturday, October 1, 2011 01 October 2011 Samstag, 1. Oktober 2011 2011年10月1日 */
     
    DECLARE @d2 DATETIME = GETDATE();  
    SELECT FORMAT( @d2, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'  
           ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result';  
    /* DateTime Result Custom Number Result 15/12/2017 123-45-6789 */
    SELECT FORMAT(cast('07:35' as time), N'hh.mm') AS r1
    ,FORMAT(cast('07:35' as time), N'hh\.mm') AS r2
    ,FORMAT(GETDATE(), N'yyyy-MM-dd HH.mm.ss') AS r3
    ,FORMAT(GETDATE(), N'yyyy-MM-dd HH:mm:ss') AS r4
    /* r1 r2 r3 r4 NULL 07.35 2017-12-15 09.43.16 2017-12-15 09:43:16 */
     
    --3. CHOOSE
    ---- 避免了多次的 CASE WHEN
    SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS r;  
    /* r Developer */
     
    --4. IIF
    ---- IIF ( boolean_expression, true_value, false_value ) 
    ---- 快速写CASE WHEN 的方法，最多也只能10层
    DECLARE @a int = 45, @b int = 40;  
    SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;  
    /* Result TRUE */
     
    --5. DATEFROMPARTS
    ----DATEFROMPARTS ( year, month, day ) 
    ----返回日期指定的年、 月和日的值。 返回类型 date
    SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result;  
    /* Result 2010-12-31 */
     
    --6. DATETIMEFROMPARTS
    ----DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) 
    ----返回datetime值的指定的日期和时间。
    SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS Result;  
    /* Result 2010-12-31 23:59:59.000 */
     
    --7. DATETIME2FROMPARTS
    ----DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) 
    ----返回datetime2值的指定的日期和时间并使用指定的精度。
    SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;  
    /* Result 2010-12-31 23:59:59 */
     
    --8. DATETIMEOFFSETFROMPARTS 
    ----DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) 
    ----返回datetimeoffset值的指定的日期和时间并使用指定的偏移量和精度。
    SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;  
    /* Result 2010-12-31 14:23:23.0000000 +12:00 */
     
    --9. TIMEFROMPARTS 
    ----TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) 
    ----返回时间值指定的时间并使用指定的精度。
    SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result;  
    /* Result 23:59:59 */
     
    --10. EOMONTH 
    ----EOMONTH ( start_date [, month_to_add ] ) 
    ----tart_date 日期表达式，指定要为其返回该月的最后一天的日期。
    ----month_to_add 指定要添加的月数的可选整数表达式start_date。
    ----返回包含指定日期的月份的最后一天（具有可选偏移量）。 返回类型 date
    DECLARE @date DATETIME = '12/1/2011';  
    SELECT EOMONTH ( @date ) AS Result,EOMONTH ( @date,-3 ) AS Result2;  
    /* Result Result2 2011-12-31 2011-09-30 */
     
    --11. PARSE
    ----PARSE ( string_value AS data_type [ USING culture ] ) 
    ----返回 SQL Server 中转换为所请求的数据类型的表达式的结果。
    ----
    --A. PARSE 为 datetime2
    SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;  
    /* Result --------------- 2010-12-13 00:00:00.0000000 */
    --B. PARSE 与货币符号
    SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result;
    /* Result --------------- 345.98 */
    --C. 使用隐式设置的语言进行 PARSE
    -- The English language is mapped to en-US specific culture 
    SET LANGUAGE 'English';  
    SELECT PARSE('12/16/2010' AS datetime2) AS Result;  
    /* Result 2010-12-16 00:00:00.0000000 */
     
    --12. TRY_PARSE
    ----TRY_PARSE ( string_value AS data_type [ USING culture ] ) 
    ----在 SQL Server 中，返回表达式的结果（已转换为请求的数据类型）；如果强制转换失败，则返回 Null。 TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型。
    ----
    --A. TRY_PARSE 的简单示例
    SELECT TRY_PARSE('Jabberwokkie' AS datetime2 USING 'en-US') AS Result;  
    /* Result --------------- NULL */
    --B. 检测 TRY_PARSE 的 Null 值
    SELECT  
        CASE WHEN TRY_PARSE('Aragorn' AS decimal USING 'sr-Latn-CS') IS NULL  
            THEN 'True'  
            ELSE 'False'  
    END  
    AS Result;  
    /* Result --------------- True */
    --C. 将 IIF 用于 TRY_PARSE 和隐式区域性设置
    SET LANGUAGE English;  
    SELECT IIF(TRY_PARSE('01/01/2011' AS datetime2) IS NULL, 'True', 'False') AS Result,TRY_PARSE('01/01/2011' AS datetime2) AS r2;  
    /* Result r2 False 2011-01-01 00:00:00.0000000 */
     
     
    --13. TRY_CONVERT
    ----TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) 
    ----返回转换为指定数据类型的值（如果转换成功）；否则返回 Null。
    --A. TRY_CONVERT 返回 null
    SELECT   
        CASE WHEN TRY_CONVERT(float, 'test') IS NULL   
        THEN 'Cast failed'  
        ELSE 'Cast succeeded'  
    END AS Result;  
    /* 复制 Result ------------ Cast failed */
    --以下示例演示表达式必须采用所需的格式。
    SET DATEFORMAT dmy;  
    SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  
    GO  
    /* Result ---------------------- NULL */
    --B. TRY_CONVERT 将失败，并出现错误
    --下面的示例演示明确不允许转换时 TRY_CONVERT 返回错误。
    SELECT TRY_CONVERT(xml, 4) AS Result;  
    /* --此语句的结果是一个错误，因为整数无法转换为 xml 数据类型。 Explicit conversion from data type int to xml is not allowed. */
    --C. TRY_CONVERT 成功
    --此示例演示表达式必须采用所需的格式。
    SET DATEFORMAT mdy;  
    SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  
    /* Result ---------------------------------- 2010-12-31 00:00:00.0000000 */