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 12»»

Split string into table, separator can be more than 1 char Expand / Collapse
Author
Message
Posted Wednesday, November 11, 2009 2:20 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Comments posted to this topic are about the item Split string into table, separator can be more than 1 char
Post #817039
Posted Thursday, November 19, 2009 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:48 AM
Points: 41, Visits: 56
I like your function, because you use a CTE.

I've been learning how and when to use CTE's and this is just another item that has helped me to better understand them.

This funciton has been done before, but I haven't seen it using a CTE.

Thanks!
Post #821769
Posted Friday, November 20, 2009 6:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 20,807, Visits: 32,740
Not a bad solution but using a recursive CTE and a multi-statement TVF this function is not very scalable.

Here is another solution to this problem:

CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by N) as N
from
a4),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #822335
Posted Friday, November 20, 2009 6:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 9,928, Visits: 11,196
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

FYI




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #822346
Posted Friday, November 20, 2009 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
It's a handy function, and a well written article. But, although I much prefer CTEs to subqueries, recursive CTEs are notoriously slow for this type of problem. Use of a tally table (also known as number table) for parsing delimited strings will do the same work in a fraction of the time, and there are even faster techniques in certain situations.

If you are not familiar with tally tables, it is really worth your while to become familiar with them. Jeff Moden's excellent article on the subject can be found here.

For an amazing group discussion about parsing delimited strings, look here.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #822348
Posted Friday, November 20, 2009 10:00 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Hi,

You're right, using the Tally table is faster:
using this parameter:
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = REPLICATE('Element01,Element02,Element03,Element04,Element05,',159)
So I got next execution times (running the same script several times):
- my function: 120 ~ 180 miliseconds
- Lynn's function: 90 ~ 140 miliseconds
- Jeff's function (a function created based on Jeff's example, using Tally table (already created on my database) : 90 ~ 140 miliseconds


Thanks!


Thanks.
Post #822523
Posted Friday, November 20, 2009 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:48 AM
Points: 41, Visits: 56
This is the function that I am currently using, is it more efficient than the CTE ?

create  FUNCTION [dbo].[func_CreateClientTableOrdered]
(
@item_list as varchar(4000)
)
RETURNS @Items Table(OrderNo int, Item varchar(60))
AS
BEGIN
declare @separator as char(1)
,@position as int
,@item as varchar(60)
,@OrderNo as int

set @OrderNo = 0
SET @separator = ','
SET @item_list = @item_list + @separator

SELECT @position = patindex('%'+@separator+'%', @item_list)

WHILE @position <> 0
BEGIN
SELECT @Item = left(@item_list, @position - 1)
SELECT @item_list = stuff(@item_list, 1, @position, '')
SELECT @position = patindex('%'+ @separator+'%', @item_list)
INSERT @Items(OrderNo, Item)
SELECT @OrderNo, @Item
SELECT @OrderNo = @OrderNo + 1
END

RETURN
END

Can someone give me a good way of proving which function would perform better?

Or point me to an article on performance tuning functions. Thanks, Paul
Post #822534
Posted Friday, November 20, 2009 10:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 20,807, Visits: 32,740
phegel (11/20/2009)
This is the function that I am currently using, is it more efficient than the CTE ?

create  FUNCTION [dbo].[func_CreateClientTableOrdered]
(
@item_list as varchar(4000)
)
RETURNS @Items Table(OrderNo int, Item varchar(60))
AS
BEGIN
declare @separator as char(1)
,@position as int
,@item as varchar(60)
,@OrderNo as int

set @OrderNo = 0
SET @separator = ','
SET @item_list = @item_list + @separator

SELECT @position = patindex('%'+@separator+'%', @item_list)

WHILE @position <> 0
BEGIN
SELECT @Item = left(@item_list, @position - 1)
SELECT @item_list = stuff(@item_list, 1, @position, '')
SELECT @position = patindex('%'+ @separator+'%', @item_list)
INSERT @Items(OrderNo, Item)
SELECT @OrderNo, @Item
SELECT @OrderNo = @OrderNo + 1
END

RETURN
END

Can someone give me a good way of proving which function would perform better?

Or point me to an article on performance tuning functions. Thanks, Paul


First, look up in this thread, I posted a function that should perform better. Also, there is a link to another thread that contains a very good discussion on string parsing.

I can see two issues with your function. One, it is using a multi-statement TVF. Second, it is using a while loop. Both of these will keep your function from scaling well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #822539
Posted Friday, November 20, 2009 10:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 20,807, Visits: 32,740
halford13 (11/20/2009)
Hi,

You're right, using the Tally table is faster:
using this parameter:
So I got next execution times (running the same script several times):
- my function: 120 ~ 180 miliseconds
- Lynn's function: 90 ~ 140 miliseconds
- Jeff's function (a function created based on Jeff's example, using Tally table (already created on my database) : 90 ~ 140 miliseconds


Thanks!


Thanks.


I think you'll find that my function uses Jeff's split method, it just incorporates the tally table into the function itself.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #822545
Posted Friday, November 20, 2009 11:15 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Hi Lynn,

Yes, I saw you used Jeff's split method.
Post #822573
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse