如何在T-SQL中将多行字符串拆分为列

Question

我有一个数据库字段，其中包含作为多行字符串存储的地址信息。

88 Park View
Hemmingdale
London

请问有没有最佳方法在select语句中将第一行、第二行和第三行作为不同的字段获取？

谢谢 Richard

- twisted

Answer 1

尝试这样做？注意这种方法有一些漏洞。

DECLARE @S VARCHAR(500), @Query VARCHAR(1000)
SELECT @S='88 Park View
           Hemmingdale
           London'

SELECT @Query=''''+ REPLACE(@S, CHAR(13),''',''')+''''
EXEC('SELECT '+@Query)

结果

88 Park View | Hemmingdale | London

- Kaf

Answer 2

我提供了一个解决方案，使用CTE来递归迭代值表，并通过换行符 CHAR(13) 分割它们，然后使用PIVOT来显示结果。您可以通过将这些添加到PIVOT中来将其扩展到其他列（超过5列）。

DECLARE @Table AS TABLE(ID int, SomeText VARCHAR(MAX))

INSERT INTO @Table VALUES(1, '88 Park View
Hemmingdale
London')

INSERT INTO @Table VALUES(2, '100 Main Street
Hemmingdale
London')

INSERT INTO @Table VALUES(3, '123 6th Street
Appt. B
Hemmingdale
London')

;WITH SplitValues (ID, OriginalValue, SplitValue, Level)
AS
(
    SELECT  ID, SomeText, CAST('' AS VARCHAR(MAX)), 0 FROM @Table

    UNION ALL

    SELECT  ID
    ,   SUBSTRING(OriginalValue, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) + 2 END, LEN(OriginalValue))
    ,   SUBSTRING(OriginalValue, 0, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) END)
    ,   Level + 1
    FROM    SplitValues
    WHERE   LEN(SplitValues.OriginalValue) > 0
)

SELECT  ID, [1] AS Level1, [2] AS Level2, [3] AS Level3, [4] AS Level4, [5] AS Level5
FROM    (
    SELECT  ID, Level, SplitValue
    FROM    SplitValues
    WHERE   Level > 0
    ) AS p
PIVOT   (MAX(SplitValue) FOR Level IN ([1], [2], [3], [4], [5])) AS pvt

结果：

ID          Level1               Level2               Level3               Level4               Level5
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1           88 Park View         Hemmingdale          London               NULL                 NULL
2           100 Main Street      Hemmingdale          London               NULL                 NULL
3           123 6th Street       Appt. B              Hemmingdale          London               NULL

- Jerad Rose

---

谢谢 @Jerad，你刚刚节省了我很多时间。 - full-stack

我在StackOverflow上发现的最好的东西之一！我向大师致敬。 - bushell

Answer 3

只是为了好玩，使用XML解决方案进行更新。这应该可以处理“.”问题，并且可以轻松扩展到更多行。

DECLARE @xml XML , @S VARCHAR(500) = '88 Park View
Hemmingdale
London'

SET @xml  = CAST('<row><col>' + REPLACE(@S,CHAR(13),'</col><col>') + '</col></row>' AS XML)

SELECT  
     line.col.value('col[1]', 'varchar(1000)') AS line1
    ,line.col.value('col[2]', 'varchar(1000)') AS line2
    ,line.col.value('col[3]', 'varchar(1000)') AS line3
FROM    @xml.nodes('/row') AS line(col)

- EricZ

---

聪明，但“88 Park View St.”怎么样？ - Tim Lehner

@TimLehner，你是对的。也许在地址中使用“.”不是一个好主意 :) - EricZ

Answer 4

我知道这很糟糕，但这样你就可以将3行分别命名为l1、l2和l3，并放在3列中：

declare @input nvarchar(max) =  'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3' + CHAR(13) + CHAR(10) + 'line 4' + CHAR(13) + CHAR(10) + 'line 5'
declare @delimiter nvarchar(10) = CHAR(13) + CHAR(10)

declare @outtable table (id int primary key identity(1,1), data nvarchar(1000) )

declare @pos int = 1
declare @temp nvarchar(1000)

if substring( @input, 1, len(@delimiter) ) = @delimiter
begin
    set @input = substring(@input, 1+len(@delimiter), len(@input))
end

while @pos > 0
begin

    set @pos = patindex('%' + @delimiter + '%', @input)

    if @pos > 0
        set @temp = substring(@input, 1, @pos-1)
    else
        set @temp = @input

    insert into @outtable ( data ) values ( @temp )

    set @input = substring(@input, @pos+len(@delimiter), len(@input))

end

select
    top 1 ISNULL(a.data,'') as l1, ISNULL(b.data,'') as l2, ISNULL(c.data,'') as l3
from
    @outtable a
        left outer join @outtable b on b.id = 2
        left outer join @outtable c on c.id = 3

在这里sqlfiddle

- fnurglewitz