| Author |
 Topic  |
|
|
pelegk2
Aged Yak Warrior
Israel
720 Posts |
 Posted - 09/01/2007 : 07:58:51
|
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
22191 Posts |
Posted - 09/01/2007 : 08:10:48
|
SELECT *
FROM dbo.SplitDelimited('11,12',',')
|
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/01/2007 : 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
Israel
720 Posts |
Posted - 09/01/2007 : 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
United Kingdom
22191 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/03/2007 : 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 - 09/03/2007 : 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
India
22460 Posts |
Posted - 09/03/2007 : 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 |
|
|
| |
Topic |
|
using split function-sql server 2005
