<

Split String to Rows and Columns...

Published on
11,280 Points
4,180 Views
1 Endorsement
Last Modified:
Approved
Saurabh Bhadauria
I am having 8 year of experience in SQL Server development , troubleshooting and support.
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>')

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>

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)

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

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

Open in new window


Finally output going to look like this, and the task is done..
String converted to rows and columns

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

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
1
Comment
1 Comment
 
LVL 66

Expert Comment

by:Jim Horn
Nice example.  Voted yes.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month