SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 using split function-sql server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 09/01/2007 :  07:58:51  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
i have made a split function using :
http://www.4guysfromrolla.com/ASPScripts/PrintPage.asp?REF=%2Fwebtech%2F031004-1.shtml
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

United Kingdom
22431 Posts

Posted - 09/01/2007 :  08:10:48  Show Profile  Reply with Quote
SELECT *
FROM
dbo.SplitDelimited('11,12',',')
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/01/2007 :  08:15:14  Show Profile  Reply with Quote
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
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 09/01/2007 :  08:22:14  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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 -:)
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/01/2007 :  09:30:55  Show Profile  Reply with Quote
"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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/01/2007 :  09:55:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/03/2007 :  02:54:22  Show Profile  Reply with Quote
Note that Vyas's general purpose UDF has poor performance, particularly on long lists ...

Kristen
Go to Top of Page

ranganath
Posting Yak Master

209 Posts

Posted - 09/03/2007 :  03:36:04  Show Profile  Reply with Quote
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)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/03/2007 :  05:46:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000