如何编写SQL函数实现Category参数匹配后返回对应Value的功能

实现逻辑 #

将两个逗号分隔的入参按位置一一对应拆分成键值对，和传入的岗位分类字段做匹配，匹配成功返回对应位置的数值，无匹配返回0。

版本1：SQL Server 2022及以上（支持STRING_SPLIT序号参数） #

函数定义：

CREATE FUNCTION dbo.GetMappedValue
(
    @Category VARCHAR(50),
    @Value VARCHAR(50),
    @JobCategory VARCHAR(50)
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 0
    -- 按序号匹配两个拆分后的字符串
    SELECT @Result = CAST(v.value AS INT)
    FROM STRING_SPLIT(@Category, ',', 1) c
    INNER JOIN STRING_SPLIT(@Value, ',', 1) v 
        ON c.ordinal = v.ordinal
    WHERE c.value = @JobCategory

    RETURN ISNULL(@Result, 0)
END
GO

版本2：SQL Server 低版本兼容写法（基于XML拆分带序号） #

如果你的数据库版本低于2022，不支持STRING_SPLIT的ordinal参数，用这个版本：

CREATE FUNCTION dbo.GetMappedValue
(
    @Category VARCHAR(50),
    @Value VARCHAR(50),
    @JobCategory VARCHAR(50)
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 0

    ;WITH CategorySplit AS (
        SELECT 
            value = n.value('.', 'varchar(50)'),
            ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
        FROM (SELECT CAST('<x>' + REPLACE(@Category, ',', '</x><x>') + '</x>' AS XML)) AS t(x)
        CROSS APPLY x.nodes('/x') AS m(n)
    ),
    ValueSplit AS (
        SELECT 
            value = n.value('.', 'int'),
            ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
        FROM (SELECT CAST('<x>' + REPLACE(@Value, ',', '</x><x>') + '</x>' AS XML)) AS t(x)
        CROSS APPLY x.nodes('/x') AS m(n)
    )
    SELECT @Result = v.value
    FROM CategorySplit c
    INNER JOIN ValueSplit v ON c.ordinal = v.ordinal
    WHERE c.value = @JobCategory

    RETURN ISNULL(@Result, 0)
END
GO

使用示例 #

DECLARE @Category VARCHAR(50)='ABC,DEF'
DECLARE @Value VARCHAR(50)='1,2'

SELECT 
    JOB AS JobNo,
    dbo.GetMappedValue(@Category, @Value, CATEGORY) AS VALUE
FROM JOB

返回结果和预期完全一致：

JOB VALUE
123 1
234 2
234 0

注意：如果@Category和@Value的元素数量不一致，只会取两者较短的长度做匹配，多出的元素不会生效。

内容的提问来源于stack exchange，提问作者bob