SQL Server 2012：提取varchar列中括号内的数据

针对你的需求，以下两种方案分别实现「提取为单独记录」和「合并为CSV格式」的效果，适配SQL Server 2012的版本特性（该版本无内置STRING_SPLIT函数）：

方案一：提取括号内内容为单独记录 #

使用递归CTE逐个定位括号位置并提取内容：

WITH RecursiveCTE AS (
    -- 初始行：定位每条记录的第一个括号位置
    SELECT 
        [FullPX],
        CHARINDEX('[', [FullPX]) AS LeftBracketPos,
        CHARINDEX(']', [FullPX]) AS RightBracketPos,
        1 AS Occurrence
    FROM [Import].[dbo].[_TMC_TEMP]
    WHERE CHARINDEX('[', [FullPX]) > 0

    UNION ALL

    -- 递归行：依次查找后续的括号位置
    SELECT 
        r.[FullPX],
        CHARINDEX('[', r.[FullPX], r.RightBracketPos + 1) AS LeftBracketPos,
        CHARINDEX(']', r.[FullPX], r.RightBracketPos + 1) AS RightBracketPos,
        r.Occurrence + 1 AS Occurrence
    FROM RecursiveCTE r
    WHERE CHARINDEX('[', r.[FullPX], r.RightBracketPos + 1) > 0
)
-- 提取括号内的子串
SELECT 
    [FullPX],
    SUBSTRING([FullPX], LeftBracketPos + 1, RightBracketPos - LeftBracketPos - 1) AS ExtractedValue
FROM RecursiveCTE
ORDER BY [FullPX], Occurrence;

方案二：合并括号内内容为CSV格式 #

基于递归CTE的结果，用FOR XML PATH拼接成CSV字符串：

WITH RecursiveCTE AS (
    SELECT 
        [FullPX],
        CHARINDEX('[', [FullPX]) AS LeftBracketPos,
        CHARINDEX(']', [FullPX]) AS RightBracketPos,
        1 AS Occurrence
    FROM [Import].[dbo].[_TMC_TEMP]
    WHERE CHARINDEX('[', [FullPX]) > 0

    UNION ALL

    SELECT 
        r.[FullPX],
        CHARINDEX('[', r.[FullPX], r.RightBracketPos + 1) AS LeftBracketPos,
        CHARINDEX(']', r.[FullPX], r.RightBracketPos + 1) AS RightBracketPos,
        r.Occurrence + 1 AS Occurrence
    FROM RecursiveCTE r
    WHERE CHARINDEX('[', r.[FullPX], r.RightBracketPos + 1) > 0
),
ExtractedValues AS (
    SELECT 
        [FullPX],
        SUBSTRING([FullPX], LeftBracketPos + 1, RightBracketPos - LeftBracketPos - 1) AS Value
    FROM RecursiveCTE
)
SELECT 
    [FullPX],
    -- 用STUFF去掉开头的逗号，拼接成CSV
    STUFF((
        SELECT ',' + ev.Value
        FROM ExtractedValues ev
        WHERE ev.[FullPX] = main.[FullPX]
        ORDER BY (SELECT NULL) -- 保持原始顺序
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS CSV_ExtractedValues
FROM [Import].[dbo].[_TMC_TEMP] main
WHERE CHARINDEX('[', main.[FullPX]) > 0
GROUP BY [FullPX];

替代方案：用数字表提取（适合大数据量） #

如果数据量较大，递归CTE效率不足，可以用系统表生成临时数字序列来定位括号：

-- 生成1到1000的临时数字序列（可按需调整上限）
WITH Numbers AS (
    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT 
    main.[FullPX],
    SUBSTRING(main.[FullPX], CHARINDEX('[', main.[FullPX], n.Num) + 1, 
              CHARINDEX(']', main.[FullPX], CHARINDEX('[', main.[FullPX], n.Num)) - CHARINDEX('[', main.[FullPX], n.Num) - 1) AS ExtractedValue
FROM [Import].[dbo].[_TMC_TEMP] main
CROSS JOIN Numbers n
WHERE n.Num <= LEN(main.[FullPX])
  AND CHARINDEX('[', main.[FullPX], n.Num) > 0
  AND (n.Num = 1 OR SUBSTRING(main.[FullPX], n.Num - 1, 1) != '[') -- 避免重复匹配同一括号
ORDER BY main.[FullPX], n.Num;

要合并为CSV的话，同样可以套用方案二中的FOR XML PATH拼接逻辑。

内容的提问来源于stack exchange，提问作者Always Try to Learn More