T-SQL中高效将列内特定格式文本规范化为表的方法

嘿，我来帮你搞定这个T-SQL的文本结构化问题！针对你这种用方括号包裹、每组冒号分隔的不定数量条目，我整理了两个实用方案，能快速把杂乱的文本转成你需要的表格格式，方便做报表分析。

如果你的SQL Server版本是2016或更新，这个方法最简洁，不用写自定义函数，直接靠STRING_SPLIT和OPENJSON就能搞定：

-- 先模拟你的源数据（替换成你实际的表和列名）
DECLARE @YourSourceTable TABLE (RawTextColumn NVARCHAR(MAX));
INSERT INTO @YourSourceTable VALUES 
('[Key1:Val1:Val2:Val3:Val4:Val5] [Key2:ValA:ValB:ValC:ValD:ValE] [Key3:X1:X2:X3:X4:X5]');

-- 核心解析逻辑，直接输出结构化结果
SELECT 
    JSON_VALUE(item.value, '$[0]') AS [Key],
    JSON_VALUE(item.value, '$[1]') AS [Column 1],
    JSON_VALUE(item.value, '$[2]') AS [Column 2],
    JSON_VALUE(item.value, '$[3]') AS [Column 3],
    JSON_VALUE(item.value, '$[4]') AS [Column 4],
    JSON_VALUE(item.value, '$[5]') AS [Column 5]
FROM @YourSourceTable
CROSS APPLY STRING_SPLIT(RawTextColumn, ' ') AS groups
CROSS APPLY (
    -- 把每组内容去掉方括号，转成JSON数组格式
    SELECT CONCAT('["', REPLACE(REPLACE(groups.value, '[', ''), ']', ''), '"]') AS json_arr
) AS prep
CROSS APPLY OPENJSON(prep.json_arr) WITH (value NVARCHAR(MAX) AS JSON) AS item
WHERE groups.value <> ''; -- 过滤可能的空字符串（比如连续空格导致的）

逻辑说明： #

1. 用STRING_SPLIT按空格拆分每个方括号包裹的条目；
2. 去掉每个条目的[和]，把冒号替换成","，拼成标准JSON数组；
3. 用OPENJSON解析JSON数组，取出每个位置的值对应到目标列。

如果要把结果存入临时表，只需要在SELECT前加SELECT ... INTO #TempReportTable，或者先定义表变量再插入数据。

如果你的SQL Server版本不支持STRING_SPLIT，可以先创建一个通用的字符串拆分函数，再分步解析：

-- 创建自定义字符串拆分函数（可以重复使用）
CREATE FUNCTION dbo.SplitString
(
    @Input NVARCHAR(MAX),
    @Delimiter NVARCHAR(50)
)
RETURNS @Output TABLE (Ordinal INT IDENTITY(1,1), Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1;
    DECLARE @EndIndex INT;

    WHILE CHARINDEX(@Delimiter, @Input, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @Input, @StartIndex);
        INSERT INTO @Output VALUES(SUBSTRING(@Input, @StartIndex, @EndIndex - @StartIndex));
        SET @StartIndex = @EndIndex + LEN(@Delimiter);
    END
    INSERT INTO @Output VALUES(SUBSTRING(@Input, @StartIndex, LEN(@Input) - @StartIndex + 1));
    RETURN;
END
GO

-- 模拟源数据
DECLARE @YourSourceTable TABLE (RawTextColumn NVARCHAR(MAX));
INSERT INTO @YourSourceTable VALUES 
('[Key1:Val1:Val2:Val3:Val4:Val5] [Key2:ValA:ValB:ValC:ValD:ValE] [Key3:X1:X2:X3:X4:X5]');

-- 分步解析成结构化表
WITH GroupedData AS (
    -- 先拆分每个方括号组，去掉括号
    SELECT 
        REPLACE(REPLACE(groups.Value, '[', ''), ']', '') AS CleanGroup
    FROM @YourSourceTable
    CROSS APPLY dbo.SplitString(RawTextColumn, ' ') AS groups
    WHERE groups.Value <> ''
),
SplitValues AS (
    -- 再把每个组按冒号拆分成单个值
    SELECT 
        CleanGroup,
        Ordinal,
        Value
    FROM GroupedData
    CROSS APPLY dbo.SplitString(CleanGroup, ':') AS vals
)
-- 用CASE把行转成列
SELECT
    MAX(CASE WHEN Ordinal = 1 THEN Value END) AS [Key],
    MAX(CASE WHEN Ordinal = 2 THEN Value END) AS [Column 1],
    MAX(CASE WHEN Ordinal = 3 THEN Value END) AS [Column 2],
    MAX(CASE WHEN Ordinal = 4 THEN Value END) AS [Column 3],
    MAX(CASE WHEN Ordinal = 5 THEN Value END) AS [Column 4],
    MAX(CASE WHEN Ordinal = 6 THEN Value END) AS [Column 5]
FROM SplitValues
GROUP BY CleanGroup;

注意事项： #

• 如果你的文本值中包含空格、冒号或方括号，上面的基础方法会出错，这种情况需要用正则表达式解析（比如SQL Server 2017+可以用STRING_AGG配合REGEXP_REPLACE，或者CLR自定义函数），但假设你的数据格式严格符合描述的规则，上面的方法完全够用；
• 可以根据实际需求调整列名、数据类型，比如把NVARCHAR(MAX)改成更合适的长度。

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