Split String to Rows and Columns...

Couple of days back I got a problem to split a string in to Rows and column...We can achieve this by using UDF or while loop, or a couple of different approaches. But  I decided to take different approach.

I found a much simpler and easier method to do this (well I think it is much easier and want to share it with you). We will be using the power of XML for to split out the string .

Below is the string ...where '^' is used as row delimiter and '|' as column delimiter...

'18355 |442866 |16 |3 |3 |1 |234.56 |1 |12 |True |True |True |True |True |True |False^ 18355 |442866 |17 |10 |10 |1 |6 |True |True |True |True |True |True |False^  
18355 |442866 |248 |0 |2 |2 |True |True |True |True |True |True |False^
18355 |442866 |268 |0 |0 |2 |False |False |False |False |False |False |False ';

So, using the power of XML, means that we need to convert the above string into a legitimate XML document. First step to convert would be to introduce proper xml tags.

We will replace row delimiter by "</rows><rows>" , column delimiter by "</column><column>" and after some adjustment our XML will be ready.

select  @String1= '<table> <rows>' + REPLACE(@BigString,'^','</rows><rows>') + '</rows> </table>'
                      select @String1=replace(replace(replace(@String1,'<rows>','<rows><column>'),'|','</column><column>'),'</rows>','</column></rows>')

Select all Open in new window

And it will look like below.

<table>
                        <rows>
                          <column>18355 </column>
                          <column>442866 </column>
                          <column>16 </column>
                          <column>3 </column>
                          <column>3 </column>
                          <column>1 </column>
                          <column>234.56 </column>
                          <column>1 </column>
                          <column>12 </column>
                          <column>True </column>
                          <column>True </column>
                          <column>True </column>
                          <column>True </column>
                          <column>True </column>
                          <column>True </column>
                          <column>False </column>
                        </rows>
                      </table>

Select all Open in new window

Now our XML is prepared,  we can easily query it easily using xquery to generate our individual columns and rows. We just save our query above into an XML variable (ie declare @xml xml) and then run a new select using xquery on that new @XML variable.

SELECT a.b.value('column[1]', 'varchar(max)') AS value1
                      	,a.b.value('column[2]', 'varchar(max)') AS value2
                      	,a.b.value('column[3]', 'varchar(max)') AS value3
                      	,a.b.value('column[4]', 'varchar(max)') AS value4
                      	,a.b.value('column[5]', 'varchar(max)') AS value5
                      	,a.b.value('column[6]', 'varchar(max)') AS value6
                      	,a.b.value('column[7]', 'varchar(max)') AS value7
                      	,a.b.value('column[8]', 'varchar(max)') AS value8
                      	,a.b.value('column[9]', 'varchar(max)') AS value9
                      	,a.b.value('column[10]', 'varchar(max)') AS value10
                      	,a.b.value('column[11]', 'varchar(max)') AS value11
                      	,a.b.value('column[12]', 'varchar(max)') AS value12
                      	,a.b.value('column[13]', 'varchar(max)') AS value13
                      	,a.b.value('column[14]', 'varchar(max)') AS value14
                      	,a.b.value('column[15]', 'varchar(max)') AS value15
                      	,a.b.value('column[16]', 'varchar(max)') AS value16
                      	,a.b.value('column[17]', 'varchar(max)') AS value17
                      	,a.b.value('column[18]', 'varchar(max)') AS value18
                      	,a.b.value('column[19]', 'varchar(max)') AS value19
                      FROM @Xml.nodes('table/rows') a(b)

Select all Open in new window

We can also generate above query dynamically with the help of CTE.  We just need to generate a expression like below.

	,a.b.value('column[2] ', 'varchar(max) ') AS value2
                      	,a.b.value('column[3] ', 'varchar(max) ') AS value3
                      	,a.b.value('column[4] ', 'varchar(max) ') AS value4
                      	,a.b.value('column[5] ', 'varchar(max) ') AS value5

Select all Open in new window

So, let us now try to use that CTE query to first generate the expression, and then to generate our select.

declare @sql nvarchar(max) , @exec_sql nvarchar(max)
                      
                      select @sql='',@exec_sql=''
                      
                      ;with SEQS as 
                      ( select ROW_NUMBER() over(order by (select null)) as Num from sys.objects)
                      
                      select @sql=@sql+' a.b.value(''column['+CAST(num as varchar(10))+']'',''varchar(max)'')' + ' as value' + CAST(num as varchar(10)) + ','
                      from SEQS
                      where Num < 20  -- and 20 is the number of columns needed
                      
                      set @sql=LEFT(@sql,LEN(@sql)-1)
                      exec sp_executesql   @exec_sql,N'@Xml xml', @Xml=@Xml

Select all Open in new window

Finally output going to look like this, and the task is done..


Below is the complete code...

Declare @BigString nvarchar(max),@String1 nvarchar(max),@xml xml
                      
                      SET @BigString = '18355|442866|16|3|3|1|234.56|1|12|True|True|True|True|True|True|False^18355|442866|17|10|10|1|6|True|True|True|True|True|True|False^18355|442866|248|0|2|2|True|True|True|True|True|True|False^18355|442866|268|0|0|2|False|False|False|False|False|False|False';
                      
                      select  @String1= '<table> <rows>' + REPLACE(@BigString,'^','</rows><rows>') + '</rows> </table>'
                      
                      select @String1=replace(replace(replace(@String1,'<rows>','<rows><column>'),'|','</column><column>'),'</rows>','</column></rows>')
                      
                      select @Xml=cast(@String1 as XML)
                      
                      declare @sql nvarchar(max) , @exec_sql nvarchar(max)
                      select @sql='',@exec_sql=''
                      
                      ;with SEQS as 
                      ( select ROW_NUMBER() over(order by (select null)) as Num from sys.objects)
                      
                      select @sql=@sql+' a.b.value(''column['+CAST(num as varchar(10))+']'',''varchar(max)'')' + ' as value' + CAST(num as varchar(10)) + ','
                      from SEQS
                      where Num < 20
                      
                      set @sql=LEFT(@sql,LEN(@sql)-1)
                      
                      set @exec_sql='select  ' + @sql + ' from @Xml.nodes(''table/rows'') a(b) '
                      
                      exec sp_executesql   @exec_sql,N'@Xml xml', @Xml=@Xml

Select all Open in new window

This Tip hopefully shows you the power of XML. if you can represent a string as XML then you can easily convert it in any required format.

Cheers,
Saurabh