在sql中拆分所有字符的字符串

I am trying to split a string in SQL on the space delimiter. The number of spaces changes, so there can be values for 4 or 5 columns. I am trying using CHARINDEX and SUBSTRING, but can't get past the first delimiter.

我试图在空格分隔符上的SQL中拆分字符串。空格数会发生变化，因此可以有4或5列的值。我正在尝试使用CHARINDEX和SUBSTRING，但无法通过第一个分隔符。

Example:

例：

input would be '444 Addison Avenue SA 5222'

输入将是'444 Addison Avenue SA 5222'

2 个解决方案

#1

To split into columns, you could use a little XML in concert with a CROSS APPLY

要拆分成列，您可以使用一个小的XML与CROSS APPLY一起使用

Example

例

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('444 Addison Avenue SA 5222')
,('1 Washington Square')


Select A.*
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace([SomeCol],' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

#2

You can achieve the same result only with XML-based splitting:

只有基于XML的拆分才能获得相同的结果：

DECLARE @tmp TABLE ([Address] varchar(50))
INSERT INTO @tmp VALUES ('444 Addison Avenue SA 5222'),('1 Washington Square')

SELECT T.[Address]
    ,AddressElements.value(N'/x[1]','varchar(max)') AS Element_1 
    ,AddressElements.value(N'/x[2]','varchar(max)') AS Element_2 
    ,AddressElements.value(N'/x[3]','varchar(max)') AS Element_3 
    ,AddressElements.value(N'/x[4]','varchar(max)') AS Element_4 
    ,AddressElements.value(N'/x[5]','varchar(max)') AS Element_5 
FROM ( 
    SELECT [Address]
        ,CAST('<x>' + REPLACE([Address],' ','</x><x>') + '</x>' AS XML) AS AddressElements 
    FROM @tmp 
) T

Results:

结果：

#1