SQL Query to split rows into rows and columns

Question

I have a line in the following format:

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

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

Output Example:

 ABC JK PLJ 

Answer 1

Not sure, I understand correctly, but if you need data in the form of three rowset columns:

 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 Tcvalue('.', '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 Tcvalue('.', 'nvarchar(200)') != '' ) t1 cross apply ( select NullIf(Tcvalue('.', '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 -------- -------- ------- ABC JK NULL PLJ 

Answer 2

Try this option -

Solution No. 1:

 DECLARE @t VARCHAR(100) SELECT @t = 'A:B:C;J:K;P:L:J;' SELECT * FROM ( SELECT token = tcvalue('.', '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:

 ---------- ABC JK PLJ 

Solution No. 2:

 DECLARE @t VARCHAR(100) SELECT @t = 'A:B:C;J:K;P:L:J;' PRINT REPLACE(REPLACE(@t, ':', ' '), ';', CHAR(13) + CHAR(13)) 

Output:

 ABC JK PLJ 

Decision No. 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 = tcvalue('@n', 'INT') , name = tcvalue('@s', 'CHAR(1)') FROM ( SELECT x = CAST('<ts = "' + REPLACE(token + ':', ':', '" n = "' + CAST(rn AS VARCHAR(10)) + '" /><ts = "') + '" />' AS XML) FROM ( SELECT token = tcvalue('.', '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 ---- ---- ---- ABC JK NULL PLJ 

Answer 3

There is no built-in separation function in SQL, but you can create this function yourself by simply following a statement like this. This solution can be reused in other cases when you want to split into several parts.

 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 as follows:

 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 

Answer 4

For large strings, I found the UDF loop approach 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