替换PARSENAME实现多段|分隔字符串的SQL关联查询方案求助

推荐：用OPENJSON实现精准拆分（SQL Server 2016及以上） #

OPENJSON能把分隔字符串转成带顺序的键值对，完美替代受4段限制的PARSENAME。假设你要从TranReference（格式如xxx|员工号|工单头Seq|工单行Seq|xxx）里提取第2段做EmployeeNum、第3段做LaborHedSeq、第4段做LaborDtlSeq，关联LaborDtl表的代码如下：

SELECT 
    t.*,
    ld.*
FROM 你的业务表 t
CROSS APPLY (
    -- 把|分隔的字符串转成JSON数组，解析出每一段和对应序号
    SELECT
        MAX(CASE WHEN SegmentIndex = 2 THEN value END) AS EmployeeNum,
        MAX(CASE WHEN SegmentIndex = 3 THEN value END) AS LaborHedSeq,
        MAX(CASE WHEN SegmentIndex = 4 THEN value END) AS LaborDtlSeq
    FROM (
        SELECT 
            value,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SegmentIndex
        FROM OPENJSON('["' + REPLACE(t.TranReference, '|', '","') + '"]')
    ) AS Segments
) AS ExtractedValues
-- 关联LaborDtl表
JOIN LaborDtl ld ON 
    ld.EmployeeNum = ExtractedValues.EmployeeNum
    AND ld.LaborHedSeq = ExtractedValues.LaborHedSeq
    AND ld.LaborDtlSeq = ExtractedValues.LaborDtlSeq

SQL Server 2022+专属：带序号的STRING_SPLIT #

如果你的SQL Server是2022或更高版本，直接用STRING_SPLIT的第三个参数启用序号功能，代码更简洁：

SELECT 
    t.*,
    ld.*
FROM 你的业务表 t
CROSS APPLY (
    SELECT
        MAX(CASE WHEN ordinal = 2 THEN value END) AS EmployeeNum,
        MAX(CASE WHEN ordinal = 3 THEN value END) AS LaborHedSeq,
        MAX(CASE WHEN ordinal = 4 THEN value END) AS LaborDtlSeq
    FROM STRING_SPLIT(t.TranReference, '|', 1) -- 第三个参数1开启序号返回
) AS ExtractedValues
JOIN LaborDtl ld ON 
    ld.EmployeeNum = ExtractedValues.EmployeeNum
    AND ld.LaborHedSeq = ExtractedValues.LaborHedSeq
    AND ld.LaborDtlSeq = ExtractedValues.LaborDtlSeq

低版本兼容：自定义带序号的拆分函数 #

如果用的是SQL Server 2016以下版本，先创建一个能返回分段序号的自定义函数：

CREATE FUNCTION dbo.SplitStringWithOrdinal(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(10)
)
RETURNS TABLE 
AS
RETURN (
    WITH SplitCTE AS (
        SELECT
            CAST(0 AS BIGINT) AS StartPos,
            CHARINDEX(@Delimiter, @InputString) AS EndPos,
            1 AS Ordinal
        UNION ALL
        SELECT
            EndPos + LEN(@Delimiter),
            CHARINDEX(@Delimiter, @InputString, EndPos + LEN(@Delimiter)),
            Ordinal + 1
        FROM SplitCTE
        WHERE EndPos > 0
    )
    SELECT
        Ordinal,
        SUBSTRING(
            @InputString,
            StartPos + CASE WHEN StartPos = 0 THEN 0 ELSE 1 END,
            CASE WHEN EndPos = 0 THEN LEN(@InputString) - StartPos ELSE EndPos - StartPos - LEN(@Delimiter) + 1 END
        ) AS Value
    FROM SplitCTE
    WHERE StartPos < LEN(@InputString)
)
GO

然后用这个函数拆分并关联：

SELECT 
    t.*,
    ld.*
FROM 你的业务表 t
CROSS APPLY (
    SELECT
        MAX(CASE WHEN Ordinal = 2 THEN Value END) AS EmployeeNum,
        MAX(CASE WHEN Ordinal = 3 THEN Value END) AS LaborHedSeq,
        MAX(CASE WHEN Ordinal = 4 THEN Value END) AS LaborDtlSeq
    FROM dbo.SplitStringWithOrdinal(t.TranReference, '|')
) AS ExtractedValues
JOIN LaborDtl ld ON 
    ld.EmployeeNum = ExtractedValues.EmployeeNum
    AND ld.LaborHedSeq = ExtractedValues.LaborHedSeq
    AND ld.LaborDtlSeq = ExtractedValues.LaborDtlSeq

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