using split function-sql server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using split function-sql server 2005

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-01 : 07:58:51

i have made a split function using :
[url]http://www.4guysfromrolla.com/ASPScripts/PrintPage.asp?REF=%2Fwebtech%2F031004-1.shtml[/url]
and found some other here too.
when i try to call the function like this (from sql server 2005):


SELECT dbo.SplitDelimited('11,12',',')

i get the message :

quote:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SplitDelimited", or the name is ambiguous.

this is the function code (but i assume it has no problem with it):


ALTER FUNCTION [dbo].[SplitDelimited]
(	
	@List nvarchar(2000),
	@SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
	Id int identity(1,1),
	Value nvarchar(100)
)
AS
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select 
			Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
		Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	
	Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 08:10:48

SELECT *
FROM dbo.SplitDelimited('11,12',',')

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 08:15:14

Note that this "loop based" approach to splitting a delimited list is significantly slower than more efficient methods if your @List contains more than about 20 items, and very slow if @List contains 1,000 items.

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-01 : 08:22:14

so what code do u recommand to use for split function??

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 09:30:55

"what code do u recommand to use for split function"

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functions

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-01 : 09:55:45

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 02:54:22

Note that Vyas's general purpose UDF has poor performance, particularly on long lists ...

Kristen

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-03 : 03:36:04

Hi,

DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = '1,2,3,6,7,8,R,a,n,g,a,n,a,t,h',
@delimiter = ','
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
SELECT @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 05:46:09

quote:
Originally posted by Kristen

Note that Vyas's general purpose UDF has poor performance, particularly on long lists ...

Kristen

Yes it is. Also you can compare some other suggestions by sommarskog
http://www.sommarskog.se/arrays-in-sql-2005.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources