SQL Query for splitting the strings into rows and

问题:

I have a string in following format:

A:B:C;J:K;P:L:J;

I want to split the string after colon(:) and start a new row after semicolon(;). Can anyone help me with a query.

Output Example:

A B C

J K

P L J

回答1:

Not sure, I understand correctly, but if you need data as three columns rowset:

declare @str nvarchar(max)
set @str = 'A:B:C;J:K;P:L:J;'

select p.[1] as Column1, p.[2] as Column2, p.[3] as Column3
from (
    select T.c.value('.', 'nvarchar(200)') [row], row_number() over (order by @@spid) rn1
    from (select cast('<r>' + replace(@str, ';', '</r><r>') + '</r>' as xml) xmlRows) [rows]
        cross apply xmlRows.nodes('/r') as T(c)
    where T.c.value('.', 'nvarchar(200)') != ''
    ) t1
    cross apply (
         select NullIf(T.c.value('.', 'nvarchar(200)'), '') row2,
            row_number() over (order by @@spid) rn
         from (select cast('<r>' + replace(t1.row, ':', '</r><r>') + '</r>' as xml) xmlRows) [rows]
            cross apply xmlRows.nodes('/r') as T(c)
    ) t2
    pivot (max(t2.row2) for t2.rn in ([1], [2], [3])) p
order by p.rn1

output

Column1  Column2  Column3
-------- -------- -------
A        B        C
J        K        NULL
P        L        J

回答2:

Try this one -

Solution #1:

DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'

SELECT * 
FROM (
     SELECT token = t.c.value('.', 'VARCHAR(100)')
     FROM
     (
          SELECT xmls = CAST('<t>' + 
               REPLACE(
                    REPLACE(@t, ':', ' '), 
                    ';', 
                    '</t><t>') + '</t>' AS XML)
     ) r
     CROSS APPLY xmls.nodes('/t') AS t(c)
) t
WHERE t.token != ''

Output:

----------
A B C
J K
P L J

Solution #2:

DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'

PRINT REPLACE(REPLACE(@t, ':', ' '), ';', CHAR(13) + CHAR(13))

Output:

A B C

J K

P L J

Solution #3:

DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'

SELECT [1], [2], [3]
FROM (
     SELECT 
            t2.id
          , t2.name
          , rn2 = ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY 1/0)  
     FROM (
          SELECT 
                id = t.c.value('@n', 'INT')
              , name = t.c.value('@s', 'CHAR(1)')
          FROM (
              SELECT x = CAST('<t s = "' + 
                    REPLACE(token + ':', ':', '" n = "' + CAST(rn AS VARCHAR(10)) 
                    + '" /><t s = "') + '" />' AS XML) 
               FROM (
                    SELECT 
                           token = t.c.value('.', 'VARCHAR(100)')
                         , rn = ROW_NUMBER() OVER (ORDER BY 1/0)
                    FROM (
                         SELECT x = CAST('<t>' + REPLACE(@t, ';', '</t><t>') + '</t>' AS XML)
                    ) r
                    CROSS APPLY x.nodes('/t') t(c)
               ) t
          ) d
          CROSS APPLY x.nodes('/t') t(c)
     ) t2
     WHERE t2.name != ''
) t3
PIVOT (
     MAX(name) FOR rn2 IN ([1], [2], [3])
) p

Output:

1    2    3
---- ---- ----
A    B    C
J    K    NULL
P    L    J

回答3:

There's no built-in split function in SQL, but you can create the function yourself by simply running a statement like this. This solution is reusable in other cases where you want to perform a multi-part split.

CREATE FUNCTION dbo.Split
(
@TextToSplit nvarchar(2000),
@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
Id int identity(1,1),
Data nvarchar(100)
) 
AS  
BEGIN 
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@TextToSplit)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@TextToSplit,1,Charindex(@SplitOn,@TextToSplit)-    1)))

    Set @RowData = Substring(@TextToSplit,Charindex(@SplitOn,@TextToSplit)+1,len(@TextToSplit))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@TextToSplit))

Return
END

Then you can run it like this:

Declare @SplitThis as varchar(2000) = 'A:B:C;J:K;P:L:J'
DECLARE @Val as varchar(500)
DECLARE MyCursor CURSOR FAST_FORWARD FOR 
    select data from split(@SplitThis,';')
OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @Val

WHILE @@FETCH_STATUS = 0
    BEGIN
    select(
        select ' ' + data 
        from split(@Val,':') 
        for xml Path(''),type).value('.','varchar(100)') as cat

 FETCH NEXT FROM MyCursor INTO @Val
END

CLOSE MyCursor 
DEALLOCATE MyCursor 

回答4:

For larger strings I found the UDF loop approach to be much more efficient than the xml approach.

create Function [Split_Text_Into_Table_Multi_Column] (
    @Row_Delimit_Char       Varchar(5)
    ,@Column_Delimit_Char   Varchar(5)
    ,@Text_To_Split         Varchar(Max) )
Returns
    @Output_Table Table (
        row_id      Int             Identity (1, 1)
        ,column_1   Varchar(Max)    Collate Latin1_General_CS_AS
        ,column_2   Varchar(Max)    Collate Latin1_General_CS_AS
        ,column_3   Varchar(Max)    Collate Latin1_General_CS_AS
    )
As
    Begin

        Declare
            @Column_Delimit_Index_1     Int
            ,@Column_Delimit_Index_2    Int
            ,@String                    Varchar(Max)
            ,@String_1                  Varchar(Max)
            ,@String_2                  Varchar(Max)
            ,@String_3                  Varchar(Max)
            ,@String_Len                Int
            ,@Delimit_Len               TinyInt


        Declare
            @Last_Delimit_Index     Int = 0

            ,@Next_Delimit_Index    Int = 1

        --always treat new line as delimiter
        Select
            @Text_To_Split = Replace(Replace(@Text_To_Split, Char(10), @Row_Delimit_Char), Char(13), @Row_Delimit_Char)

        --begin loop while next delimiter found
        While @Next_Delimit_Index > 0
        Begin

            --find next delimiter
            Select
                @Next_Delimit_Index = CharIndex(@Row_Delimit_Char, @Text_To_Split, @Last_Delimit_Index + 1)

            --get value to insert
            Select
                @String = Ltrim(Rtrim(Substring(@Text_To_Split, @Last_Delimit_Index + 1, Case
                    When @Next_Delimit_Index = 0 Then Len(@Text_To_Split) - @Last_Delimit_Index
                    Else @Next_Delimit_Index - @Last_Delimit_Index - 1
                End)))

            --Insert Non blank strings only
            If @String <> ''

            Begin

                Select
                    @Delimit_Len = Len(@Column_Delimit_Char)


                Select

                    @String_Len = DataLength(@String)/*includes trailing spaces*/

                    ,@Column_Delimit_Index_1 = 1
                    ,@Column_Delimit_Index_2 = IsNull(NullIf(CharIndex(@Column_Delimit_Char, @String, @Column_Delimit_Index_1), 0), @String_Len + 1)
                    ,@String_1 =
                                Case
                                    When @Column_Delimit_Index_2 <= @Column_Delimit_Index_1 Or
                                    @Column_Delimit_Index_2 - @Column_Delimit_Index_1 = 0 Then Null
                                    Else Substring(@String, @Column_Delimit_Index_1, @Column_Delimit_Index_2 - @Column_Delimit_Index_1)
                                End

                    ,@Column_Delimit_Index_1 = @Column_Delimit_Index_2 + @Delimit_Len
                    ,@Column_Delimit_Index_2 = IsNull(NullIf(CharIndex(@Column_Delimit_Char, @String, @Column_Delimit_Index_1), 0), @String_Len + 1)
                    ,@String_2 =
                                Case
                                    When @Column_Delimit_Index_2 <= @Column_Delimit_Index_1 Or
                                    @Column_Delimit_Index_2 - @Column_Delimit_Index_1 = 0 Then Null
                                    Else Substring(@String, @Column_Delimit_Index_1, @Column_Delimit_Index_2 - @Column_Delimit_Index_1)
                                End

                    ,@Column_Delimit_Index_1 = @Column_Delimit_Index_2 + @Delimit_Len
                    ,@Column_Delimit_Index_2 = IsNull(NullIf(CharIndex(@Column_Delimit_Char, @String, @Column_Delimit_Index_1), 0), @String_Len + 1)
                    ,@String_3 =
                                Case
                                    When @Column_Delimit_Index_2 <= @Column_Delimit_Index_1 Or
                                    @Column_Delimit_Index_2 - @Column_Delimit_Index_1 = 0 Then Null
                                    Else Substring(@String, @Column_Delimit_Index_1, @Column_Delimit_Index_2 - @Column_Delimit_Index_1)
                                End


                Insert Into
                    @Output_Table (
                        column_1
                        ,column_2
                        ,column_3)
                Values
                    (
                    @String_1
                    ,@String_2
                    ,@String_3)

            End

            --set last last char index
            Select
                @Last_Delimit_Index = @Next_Delimit_Index

        End

        Return
    End


--sample output 

Select *    
 from [Split_Text_Into_Table_Multi_Column] (',','   ','
917064578   70
917581826   66
917635915   66
917663205   66
918320481   66

918752247   ER
918892315   70
919162748   52
919169913   JS

919169927   70
919187495   52
919207137   52
919226194   70
919252530   52
919252544   KG
919269942   52
919269957   KG
919269961   47
')

output

row_id|column_1|column_2|column_3
1|917064578|70|NULL
2|917581826|66|NULL
3|917635915|66|NULL
4|917663205|66|NULL
5|918320481|66|NULL
6|918752247|ER|NULL
7|918892315|70|NULL
8|919162748|52|NULL
9|919169913|JS|NULL
10|919169927|70|NULL
11|919187495|52|NULL
12|919207137|52|NULL
13|919226194|70|NULL
14|919252530|52|NULL
15|919252544|KG|NULL
16|919269942|52|NULL
17|919269957|KG|NULL
18|919269961|47|NULL