如何在SQL Server中按RepID分组实现多段文本高亮？

核心思路 #

由于同RepID的原始RepText内容一致，我们需要：

1. 按RepID分组，对每个ID下的高亮区间按起始位置排序；
2. 通过递归CTE逐个处理每个区间，同时累计文本长度偏移量（因为每次添加<red>和</red>会增加11个字符，后续区间的位置需要相应调整）；
3. 最终取每个RepID处理完所有区间后的结果。

完整SQL实现 #

首先创建测试表（替换为你的实际表名即可）：

CREATE TABLE #TempRep (
    RepID INT,
    iBegin INT,
    iEnd INT,
    RepText NVARCHAR(MAX)
);

INSERT INTO #TempRep VALUES
(1, 10, 17, 'This is a profile of ... Moreover Toyota has been ... such as Mazda also ...'),
(1, 38, 44, 'This is a profile of ... Moreover Toyota has been ... such as Mazda also ...'),
(1, 77, 82, 'This is a profile of ... Moreover Toyota has been ... such as Mazda also ...'),
(2, 21, 24, '... flights to USA will be...weather temp is 73 degrees ...'),
(2, 82, 82, '... flights to USA will be...weather temp is 73 degrees ...');

递归处理的SQL语句：

WITH RankedIntervals AS (
    -- 给每个RepID下的高亮区间按起始位置排序
    SELECT 
        RepID,
        iBegin,
        iEnd,
        RepText,
        ROW_NUMBER() OVER (PARTITION BY RepID ORDER BY iBegin) AS IntervalRank
    FROM #TempRep
),
RecursiveHighlight AS (
    -- 初始步骤：处理每个RepID的第一个高亮区间
    SELECT 
        RepID,
        IntervalRank,
        CAST(
            SUBSTRING(RepText, 1, iBegin - 1) + 
            '<red>' + 
            SUBSTRING(RepText, iBegin, iEnd - iBegin + 1) + 
            '</red>' + 
            SUBSTRING(RepText, iEnd + 1, LEN(RepText) - iEnd)
        AS NVARCHAR(MAX)) AS HighlightedText,
        11 AS OffsetAmount -- 每次添加标签增加的字符数：<red>(5) + </red>(6) =11
    FROM RankedIntervals
    WHERE IntervalRank = 1

    UNION ALL

    -- 递归处理后续高亮区间，累计偏移量
    SELECT 
        ri.RepID,
        ri.IntervalRank,
        CAST(
            SUBSTRING(rh.HighlightedText, 1, ri.iBegin + rh.OffsetAmount - 1) + 
            '<red>' + 
            SUBSTRING(rh.HighlightedText, ri.iBegin + rh.OffsetAmount, ri.iEnd - ri.iBegin + 1) + 
            '</red>' + 
            SUBSTRING(rh.HighlightedText, ri.iEnd + rh.OffsetAmount + 1, LEN(rh.HighlightedText) - (ri.iEnd + rh.OffsetAmount))
        AS NVARCHAR(MAX)) AS HighlightedText,
        rh.OffsetAmount + 11 AS OffsetAmount
    FROM RankedIntervals ri
    JOIN RecursiveHighlight rh ON ri.RepID = rh.RepID AND ri.IntervalRank = rh.IntervalRank + 1
)
-- 取每个RepID处理完所有区间的最终结果
SELECT 
    RepID,
    HighlightedText AS RepText
FROM RecursiveHighlight
WHERE IntervalRank = (
    SELECT MAX(IntervalRank) 
    FROM RankedIntervals 
    WHERE RepID = RecursiveHighlight.RepID
)
ORDER BY RepID;

关键注意事项 #

• 确保同RepID的RepText内容完全一致，否则需要先统一原始文本；
• 若存在区间重叠的情况，需先合并重叠区间再执行上述逻辑，避免重复高亮；
• SQL Server字符串索引从1开始，语句中统一使用1作为起始位置，避免0的兼容问题。

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