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 Sunday, July 12, 2009 11:44 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
Hi Divya,
Is this a new article?


Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Post #751802
Posted Monday, July 13, 2009 12:09 AM
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
yes

--Divya
Post #751805
Posted Monday, July 13, 2009 8:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Kindred spirit, Goldie.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #752066
Posted Monday, July 13, 2009 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Eralper (7/10/2009)
Actually, I made a simple test to see that the xml function is quicker.


Actually, let's see THAT test. I already posted mine.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #752079
Posted Tuesday, December 22, 2009 11:40 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 12:12 AM
Points: 10, Visits: 44
Hi all,
sorry for the late reply...

I agreed with performance issue of SQL function incase of too large string needs to be split.

hence, i have slightly modified the SQL function which makes better performance.

Please try the below code...


CREATE function Split_fn
(
@split_string varchar(8000),
@deli_char varchar(3)
)
returns @list table
(
SeqNo int,
SplitString varchar(8000)
Primary Key (SeqNo)
)
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



With regards,
Rafidheen.M
Post #838379
Posted Tuesday, December 22, 2009 11:58 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 12:12 AM
Points: 10, Visits: 44
I have defined @split_string, SplitString (column) as varchar(8000). For 2005 or later users it can be varchar(max) (for too large string process)which will not affect the performance..
Post #838383
Posted Monday, May 9, 2011 10:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
I know it's an old post but I thought I'd provide an update just in case anyone is still thinking about using XML for splitting. Please see the following article...
http://www.sqlservercentral.com/articles/Tally+Table/72993/

If there were any doubt before, there isn't now.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1105587
Posted Monday, May 9, 2011 12:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 2:14 AM
Points: 14, Visits: 93
Jeff,

I maintain the fastest way of splitting a string is a combined process.

1. Pass the string, delim, to clr function
2. The clr function converts each item in to a fixed char width item, eg 20 chars per item.
3. The returned string is split by an inline function querying a tally table using substring.

I've tried all other methods and they are much slower.

Pure clr is slow because it is slow to pass back so many records.

Pure SQL is slow because it is slow at lookup and constructor functions.

Jeff, if you want the exact code, happy to send it through.



Post #1105684
Posted Tuesday, May 17, 2011 6:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
sam.walker (5/9/2011)
Jeff,

I maintain the fastest way of splitting a string is a combined process.

1. Pass the string, delim, to clr function
2. The clr function converts each item in to a fixed char width item, eg 20 chars per item.
3. The returned string is split by an inline function querying a tally table using substring.

I've tried all other methods and they are much slower.

Pure clr is slow because it is slow to pass back so many records.

Pure SQL is slow because it is slow at lookup and constructor functions.

Jeff, if you want the exact code, happy to send it through.


I've not found CLR code to be slow for splitters when they're done properly. Please see the code at the article I posted and test yours against the code that's in there and then post your results here. There's also a standard test data setup for your tests. A simple modification of the code to include your method will allow the automatic running and reporting of your code and the other CLR for 1-10, 10-20, 20-30, 30-40, and 40-50 random length elements across an even wider range of number of elements. Why do I want you to do it? After that article, I'm a bit burned out on testing everyone else's code.

Also, what do you do when you have items that are 21 characters in length?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1110665
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse