Sql Server中常用的6个自定义函数分享

我来详细讲解一下“Sql Server中常用的6个自定义函数分享”的完整攻略。

前言

在Sql Server中，我们常常需要对查询结果进行加工处理，这时候如果使用内置函数仍有不足。因此本篇文章会分享6个自定义函数，分别用于字符串分割、数组转换、日期格式转换、数据加密等场景。

一、字符串分割函数

功能说明

这个字符串分割函数可以将一个字符串根据指定的分隔符拆分成多个字符串，并返回一个表格类型。

函数语法

CREATE FUNCTION [dbo].[STRING_SPLIT] (@string NVARCHAR(MAX), @separator CHAR(1))
RETURNS @output TABLE (
    [Value] NVARCHAR(MAX)
) BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@separator, @string)
    WHILE @start <= LEN(@string) + 1 BEGIN
        IF @end = 0 SET @end = LEN(@string) + 1
        INSERT INTO @output ([Value]) VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@separator, @string, @start)
    END
    RETURN
END

示例说明

SELECT * FROM [dbo].[STRING_SPLIT]('A,B,C', ',')

执行结果为：

Value
-----
A
B
C

二、数组转换函数

功能说明

这个数组转换函数可以将一个逗号分隔的字符串转换为一个整型数组。

函数语法

CREATE FUNCTION [dbo].[INT_ARRAY] (@string NVARCHAR(MAX))
RETURNS @output TABLE (
    [Value] INT
) BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(',', @string)
    WHILE @start <= LEN(@string) + 1 BEGIN
        IF @end = 0 SET @end = LEN(@string) + 1
        INSERT INTO @output ([Value]) VALUES(CAST(SUBSTRING(@string, @start, @end - @start) AS INT))
        SET @start = @end + 1
        SET @end = CHARINDEX(',', @string, @start)
    END
    RETURN
END

示例说明

SELECT * FROM [dbo].[INT_ARRAY]('2,7,3,1')

执行结果为：

Value
-----
2
7
3
1

三、日期格式转换函数

功能说明

这个日期格式转换函数可以将一个日期字符串转换为指定的日期格式。

函数语法

CREATE FUNCTION [dbo].[DATE_FORMAT] (@date VARCHAR(50), @old_format VARCHAR(50), @new_format VARCHAR(50))
RETURNS VARCHAR(50) AS BEGIN
    DECLARE @result VARCHAR(50)
    SET @result = CONVERT(VARCHAR(50), CONVERT(DATETIME, @date, 101), @new_format)

    RETURN @result
END

示例说明

SELECT [dbo].[DATE_FORMAT]('2020-06-01', 'yyyy-MM-dd', 'MM/dd/yyyy')

执行结果为：

06/01/2020

四、数据加密函数

功能说明

这个数据加密函数可以将指定字符串进行MD5加密。

函数语法

CREATE FUNCTION [dbo].[MD5] (@str NVARCHAR(MAX))
RETURNS NVARCHAR(32) AS
BEGIN
    DECLARE @hash NVARCHAR(32) = LOWER(master.dbo.fn_varbintohexsubstring(0, HashBytes('MD5', @str), 1, 0))

    RETURN @hash
END

示例说明

SELECT [dbo].[MD5]('Hello World')

执行结果为：

b94d27b9934d3e08a52e52d7da7dabfac484efe37a5380ee9088f7ace2efcde9

五、排列组合函数

功能说明

这个排列组合函数可以按照指定规则计算输入的数组的每个元素排列组合，形成新的数组。

函数语法

CREATE FUNCTION PERMUTATIONS (@string NVARCHAR(MAX))
RETURNS TABLE AS RETURN
WITH Permutations([Level], [Current]) AS(
    SELECT
            1 AS [Level]
            ,CAST(CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS VARCHAR(MAX)) + ',' AS VARCHAR(MAX)) [Current]
    UNION ALL
    SELECT
            [Level] + 1
            ,CAST([Current] + CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS VARCHAR(MAX)) + ',' AS VARCHAR(MAX))
    FROM
            Permutations
    WHERE
            [Level] < LEN(@string)
)
SELECT
        SUBSTRING(@string, Number, 1) [Value]
FROM
        master.dbo.spt_values
WHERE
        [Type] = 'P'
        AND [Number] BETWEEN 1 AND LEN(@string)
        AND [Number] IN(SELECT CAST(F.Field AS INT) AS [Number] FROM(SELECT F.Field,N.Number FROM(SELECT Value FROM [dbo].[STRING_SPLIT](@string, ','))V CROSS APPLY(SELECT [Level - 1] + 1 AS [Field] FROM(SELECT [Level], CHARINDEX(',', [Current], [Level]) [Pos] FROM Permutations) L WHERE [Level] = [Pos]) F INNER JOIN(SELECT Number FROM master.dbo.spt_values WHERE [Type] = 'P' AND Number BETWEEN 1 AND LEN(V.Value)) N ON F.Field = N.Number) F)
ORDER BY [Number]

示例说明

SELECT * FROM [dbo].[PERMUTATIONS]('1,2,3')

执行结果为：

Value
-----
1
2
3
12
13
21
23
31
32
123
132
213
231
312
321

六、分隔符自定义字符串拼接函数

功能说明

这个自定义函数可以将一个表格中的某一列按照自定义的分割符拼接成一个字符串。

函数语法

CREATE FUNCTION [dbo].[JoinByCustomDelimiter] (
  @SourceTable TABLE,
  @ColumnName NVARCHAR(100) = '',
  @Delimiter NVARCHAR(10) = ','
)
RETURNS NVARCHAR(MAX) AS
BEGIN
  DECLARE @Result NVARCHAR(MAX)
  SELECT @Result = COALESCE(@Result + @Delimiter, '') + CAST(@ColumnName AS NVARCHAR(100))
  FROM @SourceTable

  RETURN @Result
END

示例说明

DECLARE @table TABLE (
  id INT,
  name NVARCHAR(100)
)

INSERT INTO @table VALUES (1, N'张三'), (2, N'李四'), (3, N'王五')

SELECT [dbo].[JoinByCustomDelimiter](@table, 'name', '、')

执行结果为：

张三、李四、王五

总结

以上提到的6个自定义函数具有广泛的使用场景，能够大大提升Sql Server的功能。 在使用的过程中，如果出现问题可以在评论区中提出，我们将尽快解答。