<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Split String to Rows and Columns...

Published on
11,340 Points
4,240 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 67

Expert Comment

by:Jim Horn
Nice example.  Voted yes.
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month