从SQL Server列提取含空格的嵌套CSV数据，求T-SQL/SSIS方案

一、T-SQL解决方案 #

利用宠物条目的固定结构（最后一个字段为数字），通过递归CTE拆分条目，再拆分每个条目的字段：

示例代码 #

1. 创建测试表并插入数据：

CREATE TABLE #PersonPets (Person VARCHAR(50), House VARCHAR(50), Pets VARCHAR(MAX))
INSERT INTO #PersonPets VALUES
('person1', 'house1', '"dog,rex,4 cat,sally,7 lizard,mr scales,4"'),
('person2', 'house2', '"fish,wanda,10 dog,bud,8"')

2. 递归拆分并结构化数据：

;WITH SplitPets AS (
    SELECT 
        Person,
        House,
        TRIM(REPLACE(Pets, '"', '')) AS RemainingPets,
        PATINDEX('% [0-9] %', TRIM(REPLACE(Pets, '"', '')) + ' ') AS EndPos,
        CASE WHEN PATINDEX('% [0-9] %', TRIM(REPLACE(Pets, '"', '')) + ' ') > 0 
             THEN LEFT(TRIM(REPLACE(Pets, '"', '')), PATINDEX('% [0-9] %', TRIM(REPLACE(Pets, '"', '')) + ' ') + 1)
             ELSE TRIM(REPLACE(Pets, '"', ''))
        END AS PetEntry
    FROM #PersonPets
    UNION ALL
    SELECT 
        Person,
        House,
        TRIM(SUBSTRING(RemainingPets, EndPos + 1, LEN(RemainingPets))),
        PATINDEX('% [0-9] %', TRIM(SUBSTRING(RemainingPets, EndPos + 1, LEN(RemainingPets))) + ' ') AS EndPos,
        CASE WHEN PATINDEX('% [0-9] %', TRIM(SUBSTRING(RemainingPets, EndPos + 1, LEN(RemainingPets))) + ' ') > 0 
             THEN LEFT(TRIM(SUBSTRING(RemainingPets, EndPos + 1, LEN(RemainingPets))), PATINDEX('% [0-9] %', TRIM(SUBSTRING(RemainingPets, EndPos + 1, LEN(RemainingPets))) + ' ') + 1)
             ELSE TRIM(SUBSTRING(RemainingPets, EndPos + 1, LEN(RemainingPets)))
        END AS PetEntry
    FROM SplitPets
    WHERE LEN(TRIM(RemainingPets)) > 0
)
SELECT 
    Person,
    House,
    LEFT(PetEntry, CHARINDEX(',', PetEntry) - 1) AS Species,
    SUBSTRING(PetEntry, CHARINDEX(',', PetEntry) + 1, CHARINDEX(',', PetEntry, CHARINDEX(',', PetEntry) + 1) - CHARINDEX(',', PetEntry) - 1) AS Name,
    RIGHT(PetEntry, LEN(PetEntry) - CHARINDEX(',', PetEntry, CHARINDEX(',', PetEntry) + 1)) AS Age
FROM SplitPets
WHERE LEN(PetEntry) > 0
ORDER BY Person, House

逻辑说明 #

• 先移除Pets列的前后引号，清理字符串。
• 通过PATINDEX定位每个宠物条目结束的位置（数字后的空格），递归拆分出所有条目。
• 再对每个条目按逗号拆分，提取物种、名字、年龄三个字段。

二、SSIS解决方案 #

推荐方案：Script Component（数据流转换） #

内置拆分工具无法区分数据内空格和条目分隔空格，用Script Component可自定义拆分逻辑，以下是C#实现的详细步骤：

操作步骤

1. 新建SSIS包，添加OLE DB源，连接到SQL Server并选择源表。
2. 拖拽Script Component到数据流，选择「转换」类型，连接OLE DB源的输出。
3. 双击Script Component，在「输入列」中勾选Person、House、Pets三列。
4. 切换到「输入和输出」选项卡：
   • 点击「添加输出」，命名为PetOutput。
   • 在PetOutput下添加三个输出列：Species（字符串类型）、Name（字符串类型）、Age（可设为字符串或整数）。
5. 点击「编辑脚本」，替换Input0_ProcessInputRow方法的代码：

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // 移除Pets字段的前后引号
    string petsContent = Row.Pets.Trim('"');
    List<string> petEntries = new List<string>();
    int currentIndex = 0;
    int totalLength = petsContent.Length;

    while (currentIndex < totalLength)
    {
        // 定位第二个逗号（分隔名字和年龄的逗号）
        int firstComma = petsContent.IndexOf(',', currentIndex);
        if (firstComma == -1) break;
        int secondComma = petsContent.IndexOf(',', firstComma + 1);
        if (secondComma == -1) break;

        // 从第二个逗号后找到数字结束的位置，跳过后续空格作为条目分隔
        int entryEnd = secondComma + 1;
        while (entryEnd < totalLength)
        {
            if (char.IsDigit(petsContent[entryEnd]))
            {
                entryEnd++;
            }
            else if (char.IsWhiteSpace(petsContent[entryEnd]))
            {
                break;
            }
            else
            {
                // 处理特殊情况（如年龄后无空格直接到结尾）
                entryEnd++;
            }
        }

        // 提取当前宠物条目并加入列表
        string currentEntry = petsContent.Substring(currentIndex, entryEnd - currentIndex).Trim();
        petEntries.Add(currentEntry);
        currentIndex = entryEnd;
    }

    // 处理最后一个无后续空格的条目
    if (currentIndex < totalLength)
    {
        string lastEntry = petsContent.Substring(currentIndex).Trim();
        if (!string.IsNullOrEmpty(lastEntry))
        {
            petEntries.Add(lastEntry);
        }
    }

    // 将每个条目拆分后输出到行
    foreach (string entry in petEntries)
    {
        // 按逗号拆分3个部分（避免名字含逗号的情况）
        string[] fieldParts = entry.Split(new char[] { ',' }, 3);
        if (fieldParts.Length == 3)
        {
            PetOutputBuffer.AddRow();
            PetOutputBuffer.Person = Row.Person;
            PetOutputBuffer.House = Row.House;
            PetOutputBuffer.Species = fieldParts[0].Trim();
            PetOutputBuffer.Name = fieldParts[1].Trim();
            PetOutputBuffer.Age = fieldParts[2].Trim();
        }
    }
}

6. 保存脚本并关闭编辑器，添加OLE DB目标到数据流，连接目标表并完成列映射，运行包即可。

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