Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Split string using XML Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 10:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 5, 2014 1:48 AM
Points: 143, Visits: 551
Comments posted to this topic are about the item Split string using XML

--Divya
Post #741532
Posted Thursday, June 25, 2009 12:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 23, 2011 8:10 AM
Points: 1, Visits: 19
Very useful.
Post #741565
Posted Thursday, June 25, 2009 1:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 8:01 AM
Points: 120, Visits: 297
what about performance?
Post #741592
Posted Thursday, June 25, 2009 2:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 10:16 AM
Points: 36, Visits: 6
Good. Different approach.
Post #741617
Posted Thursday, June 25, 2009 2:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:54 AM
Points: 1,419, Visits: 440
Be aware of the possibility of markup characters within your data.
Eg, the following split will fail:
Declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A<2,B>4,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

Post #741622
Posted Thursday, June 25, 2009 3:50 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 12:12 AM
Points: 10, Visits: 44
Hi all,

Its a new way to split the string using XML. I have written a SQL function to split the string without using XML. Please refer the below code...
CREATE function Split_fn
(
@split_string varchar(max),
@deli_char varchar(3)
)
returns @list table
(
SeqNo int,
SplitString varchar(max)
)
as
begin

declare @from_loc int
declare @to_loc int

if charindex(@deli_char,@split_string,0) <= 0
begin
insert into @list(seqno, SplitString) values (1, @split_string)
return
end

if charindex(@deli_char,@split_string,0) > 0
begin
select @from_loc = 0
select @to_loc = charindex(@deli_char,@split_string,0)
end

if charindex(@deli_char,@split_string,0) <= 0
begin
select @to_loc = null
end

while @to_loc is not null
begin

if substring(@split_string,@from_loc, @to_loc - @from_loc) <> ''
begin
insert into @list(seqno, SplitString)
select isnull(max(seqno),0) + 1, substring(@split_string,@from_loc, @to_loc - @from_loc)
from @list
end

select @from_loc = charindex(@deli_char,@split_string,@from_loc+len(@deli_char)) + len(@deli_char)
select @to_loc = charindex(@deli_char,@split_string,@from_loc)


if @to_loc = 0
begin
if substring(@split_string,@from_loc, (len(@split_string) - @from_loc) + len(@deli_char)) <> ''
begin
insert into @list(seqno, SplitString)
select isnull(max(seqno),0) + 1, substring(@split_string,@from_loc, (len(@split_string) - @from_loc) + len(@deli_char))
from @list
end
select @to_loc = null
end
end
return
end


go
select * from dbo.split_fn('raja,ravi,prabhu',',')

Rafidheen.M
Post #741651
Posted Thursday, June 25, 2009 4:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 2,281, Visits: 4,226
This technique was documented by Erland Sommarskog, SQL Server MVP in 2004. The XML and other methods can be found in the classic article "Arrays and Lists in SQL Server".

For SQL Server 2000, see http://www.sommarskog.se/arrays-in-sql-2000.html and for SQL Server 2005, see http://www.sommarskog.se/arrays-in-sql-2005.html


SQL = Scarcely Qualifies as a Language
Post #741658
Posted Thursday, June 25, 2009 4:43 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 8:16 AM
Points: 15, Visits: 139
What about using Tally tables to do the same:

http://www.sqlservercentral.com/articles/T-SQL/62867/
Post #741678
Posted Thursday, June 25, 2009 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2011 12:37 AM
Points: 2, Visits: 19
We did performance and scaling test to split the comma separated string value using XML query and SQL function.

The plan was better with XML split than SQL function; however XML query performance degraded when number of concurrent users increase. SQL function did better in scalability test.
Post #741751
Posted Thursday, June 25, 2009 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2011 12:37 AM
Points: 2, Visits: 19
We did performance and scaling test to split the comma separated string value using XML query and SQL function.

The plan was better with XML split than SQL function; however XML query performance degraded when number of concurrent users increase. SQL function did better in scalability test.
Post #741752
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse