SUBSTRING(@List, StartPos, EndPos - StartPos) as value
FROM Tokens
WHERE EndPos <> 0
)
</div>
CREATE FUNCTION [dbo].[Split](
@List VARCHAR(8000),
@Del VARCHAR(1)
) RETURNS TABLE
AS
RETURN (
WITH Tokens AS (
SELECT
StartPos = 1,
EndPos = CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + @Del)
UNION ALL
SELECT
EndPos + 1,
CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + @Del, EndPos + 1)
FROM Tokens
WHERE EndPos > 0
)
SELECT
SUBSTRING(@List, StartPos, EndPos - StartPos) as value
FROM Tokens
WHERE EndPos <> 0
)
This code is using recursion and would fail if you have a lot of items (above 100). Instead try Jeff Moden's code from sqlserverCentral. It is fast, can handle varchar(max) and doesn't use recursion.
This code is using recursion and would fail if you have a lot of items (above 100). Instead try Jeff Moden's code from sqlserverCentral. It is fast, can handle varchar(max) and doesn't use recursion.
Before posting, sometimes is usefull to read a bit. All that you wrote is written in the thread I linked. If you use Option Maxrecursion it will not fail. There are posted different versions of fnSplit function, some of them support varchar(max) - in fact,
all of them can support it, it's just a question of datatypes, nothing else. There are posted functions that does not use CTE too.
I gave a link and I told that UDF I posted here (which is not mine) performed best in my tests. How much faster performs function from Jeff Moden's code from sqlserverCentral i do not know. Do you? I didn't test it and I can't tell if it is fast or
not.
The question is: How many times you will send string longer than 8000 characters and which has more than hundred items. I never did that. And I would create fnSplitBig to handle that cases, but for everyday's jobs I'll use the fastest one.
I couldn't find split function you mentioned, if you post it, I will compare it with all others and post result back. I think that's fair enough.
P.S.
I saw comments that CTE's hurts performance, but I believe more to numbers and tests than to someone's opinion - although I agree CTE can hurt performance in some cases, but plain select can do it too.
ADDED LATER:
I do not think this is the right place to discuss about split function efficiency. You can make comments on
The reason Cetin joined this thread was my post today in UT in the recent discussion we had on a similar topic on splitting strings. I don't remember right now if you already made UT account (I think you did), if yes, this is the thread
Re: Passing list of keys to a sproc to get recordset Thread #1425870 Message #1426082
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
I can tell you right now that the CTE in Cetin's link will not perform as well as my UDF. Also, use of a permanent numbers (tally) table performs better than one created on the fly.
I do apologize for the bug in my query to not respect the delimiter passed in! It is fixed now in the original thread.
The reason Cetin joined this thread was my post today in UT in the recent discussion we had on a similar topic on splitting strings. I don't remember right now if you already made UT account (I think you did), if yes, this is the thread
Re: Passing list of keys to a sproc to get recordset Thread #1425870 Message #1426082
From what I see on first sight, it will not outperform most of the functions I've alredy tested.
It's just one more version of function that uses numbers table, but it creates numbers table with CTE every time it need it. That brings unnecessary resource usage and degrades performance.
yrb.yogi
Star
14460 Points
2402 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 01:53 PM|LINK
so please also update the post here.
and thnx dude....
.Net All About
CetinBasoz
Member
2 Points
1 Post
Re: Insert multiple rows from sql statement?
Sep 30, 2009 04:08 PM|LINK
This code is using recursion and would fail if you have a lot of items (above 100). Instead try Jeff Moden's code from sqlserverCentral. It is fast, can handle varchar(max) and doesn't use recursion.
Niikola
Contributor
3998 Points
762 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 05:31 PM|LINK
Before posting, sometimes is usefull to read a bit. All that you wrote is written in the thread I linked. If you use Option Maxrecursion it will not fail. There are posted different versions of fnSplit function, some of them support varchar(max) - in fact, all of them can support it, it's just a question of datatypes, nothing else. There are posted functions that does not use CTE too.
I gave a link and I told that UDF I posted here (which is not mine) performed best in my tests. How much faster performs function from Jeff Moden's code from sqlserverCentral i do not know. Do you? I didn't test it and I can't tell if it is fast or not.
The question is: How many times you will send string longer than 8000 characters and which has more than hundred items. I never did that. And I would create fnSplitBig to handle that cases, but for everyday's jobs I'll use the fastest one.
I couldn't find split function you mentioned, if you post it, I will compare it with all others and post result back. I think that's fair enough.
P.S.
I saw comments that CTE's hurts performance, but I believe more to numbers and tests than to someone's opinion - although I agree CTE can hurt performance in some cases, but plain select can do it too.
ADDED LATER:
I do not think this is the right place to discuss about split function efficiency. You can make comments on
http://forum.lessthandot.com/viewtopic.php?f=17&t=7566&start=0&st=0&sk=t&sd=a
or
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/split-string-in-sql-server-2005-clr-vs-t
Naom
All-Star
36004 Points
7901 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 05:40 PM|LINK
Hi Nikola,
I'm answering your last post. Here is the link Cetin meant http://www.foxite.com/archives/sqlexec-with-a-local-cursor-0000192577.htm
The reason Cetin joined this thread was my post today in UT in the recent discussion we had on a similar topic on splitting strings. I don't remember right now if you already made UT account (I think you did), if yes, this is the thread
Re: Passing list of keys to a sproc to get recordset Thread #1425870 Message #1426082
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
Emtucifor
Member
40 Points
11 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 06:17 PM|LINK
I can tell you right now that the CTE in Cetin's link will not perform as well as my UDF. Also, use of a permanent numbers (tally) table performs better than one created on the fly.
I do apologize for the bug in my query to not respect the delimiter passed in! It is fixed now in the original thread.
Niikola
Contributor
3998 Points
762 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 06:19 PM|LINK
From what I see on first sight, it will not outperform most of the functions I've alredy tested.
It's just one more version of function that uses numbers table, but it creates numbers table with CTE every time it need it. That brings unnecessary resource usage and degrades performance.
I'll test it tomorrow
Niikola
Contributor
3998 Points
762 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 06:21 PM|LINK
Until I see faster one than yours, you are my hero ;-)
Naom
All-Star
36004 Points
7901 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 06:58 PM|LINK
Check the thread on LTD. The link is http://www.sqlservercentral.com/articles/T-SQL/63003/
I think we may finish that discussion there.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
Niikola
Contributor
3998 Points
762 Posts
Re: Insert multiple rows from sql statement?
Sep 30, 2009 07:07 PM|LINK
I know that article, but I didn't see anything faster :-)
I agree