tsql - 使用 T-SQL 将值拆分为多行的问题

Question

我有一个 Sql Server 2K8 R2 DB，其中有一个表，该表有一列包含多个值，由 (char 13 和 char 10) 分隔。

我正在构建一个脚本来以正确规范化的模式导入数据。

我的源表包含如下内容:

ID   |    Value
________________
1    |    line 1
          line 2
________________
2    |    line 3
________________
3    |    line 4
          line 5
          line 6
________________

等等。

[编辑] 仅供引用，Id 是整数，值为 nvarchar(3072) [/编辑]

我想要的是查询表以输出这样的东西:

ID   |    Value
________________
1    |    line 1
________________
1    |    line 2
________________
2    |    line 3
________________
3    |    line 4
________________
3    |    line 5
________________
3    |    line 6
________________

我在 SO 上以及在网络上阅读了很多答案，我发现使用 master..sptvalues应该是解决方案。特别是，我试图重现问题的解决方案 Split one column into multiple rows .
但是，没有成功(怀疑有两个字符导致问题)。

到现在为止，我写了这个查询:

SELECT
    T.ID,
    T.Value, 
    RIGHT(LEFT(T.Value,spt.Number-1),
    CHARINDEX(char(13)+char(10),REVERSE(LEFT(char(13)+char(10)+T.Value,spt.Number-1)))) as Extracted
FROM 
    master..spt_values spt,
    ContactsNew T
WHERE
    Type = 'P' AND 
    spt.Number BETWEEN 1 AND LEN(T.Value)+1
    AND
        (SUBSTRING(T.Value,spt.Number,2) = char(13)+char(10) OR SUBSTRING(T.Value,spt.Number,2) = '')

不幸的是，此查询正在返回:

ID   |    Value    |   Extracted
________________________________   
1    |    line 1   |   <blank>
          line 2   |   
________________________________   
1    |    line 1   |   line 2
          line 2   |   
________________________________   
2    |    line 3   |   <blank>
________________________________   
3    |    line 4   |   <blank>
          line 5   |
          line 6   |
________________________________   
3    |    line 4   |   line 5
          line 5   |   line 6
          line 6   |
________________________________   
3    |    line 4   |   line 6
          line 5   |
          line 6   |
________________________________  

<blank>是一个空字符串，而不是空字符串。

我很感激一些帮助来调整我的查询。

[Edit2] 我的源表包含少于 200 条记录，并且不需要性能，所以我的目标是一个简单的解决方案而不是一个高效的解决方案 [Edit2]

[Edit3] 源数据库是只读的。我无法添加存储过程、函数或 clr 类型。我必须在单个查询中执行此操作。 [Edit3]

[Edit4] 奇怪的东西......似乎空格也被视为分隔符。

如果我运行以下查询:

SELECT
    T.ID,
    replace(T.Value, '#', ' '), 
    replace(RIGHT(
        LEFT(T.Value,spt.Number-1),
        CHARINDEX( char(13) + char(10),REVERSE(LEFT(char(10) + char(13)+T.Value,spt.Number-0)))
        ), '#', ' ')
FROM 
    master..spt_values spt,
    (   
        select contactID, 
        replace(Value,' ', '#') Value
        from ContactsNew where Value is not null
    ) T
WHERE
    Type = 'P' AND 
    spt.Number BETWEEN 1 AND LEN(T.Value)+1
    AND
        (SUBSTRING(T.Value,spt.Number,2) =  char(13) + char(10) OR SUBSTRING(T.Value,spt.Number,1) = '')

在运行此查询时，我得到了正确的返回次数(但是，仍然有错误的值):

SELECT
    T.ID,
    T.Value, 
    RIGHT(
        LEFT(T.Value,spt.Number-1),
        CHARINDEX( char(13) + char(10),REVERSE(LEFT(char(10) + char(13)+T.Value,spt.Number-0)))
        )
FROM 
    master..spt_values spt,
    (   
        select contactID, 
        Value
        from ContactsNew where Value is not null
    ) T
WHERE
    Type = 'P' AND 
    spt.Number BETWEEN 1 AND LEN(T.Value)+1
    AND
        (SUBSTRING(T.Value,spt.Number,2) =  char(13) + char(10) OR SUBSTRING(T.Value,spt.Number,1) = '')

也 split 空间

Answer 1

编辑#1:我删除了原始答案文本。尝试以下查询。我稍微修改了你的逻辑。如果您对此有任何疑问，请随时在评论中提问。如果您需要另一个拆分分隔符，只需引入另一个嵌套查询以用 CHAR(13)+CHAR(10) 替换该分隔符。

SELECT 
* 
FROM 
(
    SELECT
        T.ID,
        T.Value,
        CASE
            WHEN CHARINDEX(CHAR(13) + CHAR(10), SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1)) > 0 THEN
                LEFT(
                    SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1), 
                    CHARINDEX(CHAR(13) + CHAR(10), SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1)) - 1)
 /* added by Steve B. see comments for the reasons */
        when len(T.Value) = spt.Number then right(t.Value, spt.number -1) 
 /* end of edit */
            ELSE
                SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1)
        END EXTRACTED
    FROM 
        master..spt_values spt,
        ContactsNew T
    WHERE
        Type = 'P' AND 
        spt.Number BETWEEN 1 AND LEN(T.Value)+1
) X
WHERE 
    EXTRACTED <> '' AND
    (
        LEFT(X.VALUE, LEN(EXTRACTED)) = EXTRACTED OR 
        X.Value LIKE '%' + CHAR(13) + CHAR(10) + EXTRACTED + CHAR(13) + CHAR(10) + '%' OR
        X.Value LIKE '%' + CHAR(13) + CHAR(10) + EXTRACTED
    )