SQL实现：从字符串提取多值并按指定规则排序

需求说明 #

Notes字段存储了多段格式为Request Notes: [Type] - 内容的文本，需要为每个ClientID提取所有对应的Type，并按**从新到旧（原字符串末尾到开头）**的顺序排序，最后输出ClientID、Type和对应的顺序编号。

测试数据 #

CREATE TABLE #TestData
(
    ClientID int,
    Notes varchar(8000)
)
insert into #TestData
(
    ClientID,
    Notes
)
select
    1,
    'Request Notes: VAR - abc abc abc abc abc'
union all
select
    2,
    'Request Notes: OZR - abc abc abc abc abc Request Notes: ACC - abc abc abc abc abc Request Notes: TYU - abc abc abc abc abc'
union all
select
    3,
    'Request Notes: TYU - abc abc abc abc abc Request Notes: VAR - abc abc abc abc abc'

预期输出 #

--Expected Output

Client ID       Type    Order
1               VAR     1
2               TYU     1
2               ACC     2
2               OZR     3
3               VAR     1
3               TYU     2

现有代码（仅能提取单个类型） #

DECLARE @Text varchar(500) = 'Request Notes: OZR - abc abc abc abc abc Request Notes: ACC - abc abc abc abc abc Request Notes: TYU - abc abc abc abc abc'

SELECT TRIM(REPLACE(REPLACE(SUBSTRING(@Text, CHARINDEX(':', @Text), CHARINDEX('-',@text) - CHARINDEX(':', @Text) + Len('-')),':',''),'-',''))

完整解决方案 #

方案1：适用于SQL Server 2016+（使用STRING_SPLIT） #

利用STRING_SPLIT快速拆分字符串，再提取类型并反转排序：

WITH SplitNotes AS (
    SELECT 
        ClientID,
        LTRIM(RTRIM(value)) AS NoteSegment
    FROM #TestData
    CROSS APPLY STRING_SPLIT(Notes, 'Request Notes: ')
    WHERE LTRIM(RTRIM(value)) <> '' -- 过滤拆分后产生的空条目
),
ExtractedTypes AS (
    SELECT 
        ClientID,
        -- 提取冒号与减号之间的Type内容
        TRIM(SUBSTRING(NoteSegment, CHARINDEX(':', NoteSegment) + 1, CHARINDEX('-', NoteSegment) - CHARINDEX(':', NoteSegment) - 1)) AS [Type],
        -- 按原始顺序给片段编号
        ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY (SELECT NULL)) AS OriginalOrder
    FROM SplitNotes
)
SELECT 
    ClientID AS [Client ID],
    [Type],
    -- 反转原始顺序，得到从新到旧的排序编号
    DENSE_RANK() OVER (PARTITION BY ClientID ORDER BY OriginalOrder DESC) AS [Order]
FROM ExtractedTypes
ORDER BY ClientID, [Order];

方案2：兼容SQL Server 2016之前版本（递归CTE拆分） #

如果使用低版本SQL Server，用递归CTE手动拆分字符串：

WITH SplitNotes AS (
    SELECT 
        ClientID,
        Notes,
        -- 定位第一个Request Notes片段的起始位置
        CHARINDEX('Request Notes: ', Notes) + LEN('Request Notes: ') AS StartPos,
        -- 定位下一个Request Notes片段的起始位置（作为当前片段的结束）
        CHARINDEX('Request Notes: ', Notes, CHARINDEX('Request Notes: ', Notes) + LEN('Request Notes: ')) AS EndPos
    FROM #TestData
    WHERE Notes LIKE '%Request Notes: %'
    UNION ALL
    SELECT 
        ClientID,
        Notes,
        EndPos + LEN('Request Notes: '),
        CHARINDEX('Request Notes: ', Notes, EndPos + LEN('Request Notes: '))
    FROM SplitNotes
    WHERE EndPos > 0
),
ExtractedSegments AS (
    SELECT 
        ClientID,
        -- 提取单个Note片段
        LTRIM(RTRIM(CASE 
            WHEN EndPos = 0 THEN SUBSTRING(Notes, StartPos, LEN(Notes) - StartPos + 1) 
            ELSE SUBSTRING(Notes, StartPos, EndPos - StartPos) 
        END)) AS NoteSegment
    FROM SplitNotes
),
ExtractedTypes AS (
    SELECT 
        ClientID,
        TRIM(SUBSTRING(NoteSegment, CHARINDEX(':', NoteSegment) + 1, CHARINDEX('-', NoteSegment) - CHARINDEX(':', NoteSegment) - 1)) AS [Type],
        -- 按原始出现顺序编号
        ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY StartPos) AS OriginalOrder
    FROM ExtractedSegments
)
SELECT 
    ClientID AS [Client ID],
    [Type],
    DENSE_RANK() OVER (PARTITION BY ClientID ORDER BY OriginalOrder DESC) AS [Order]
FROM ExtractedTypes
ORDER BY ClientID, [Order];

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