Help required for Splitting up string variable using comma separator

I need a help in SQL Server 2000.

I am having a string variable in the format like -- (1,23,445,5,12)

I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.

Can anyone help me out in splitting the variable using the comma separator...

You can just use the split command e.g.

Dim sAs String ="1,23,445,5,12"Dim splitAs String() = s.Split(",")For Each itemAs String In split Response.Write("Item: " & item &"<br>")Next

|||

I doesnt want this in VB.NET.

I want the same using SQL query in SQL Server 2000.

|||You'll have to create a function to do this. Here's a starting point:http://www.madprops.org/cs/blogs/mabster/archive/2005/12/05/T_2D00_SQL-to-Split-a-varchar-into-Words.aspx|||

Oh, sorry. If you want to do this in SQL, it's a bit harder as it doesn't really have built in string manipulation functions. If you were using a later version of SQL Server you could have registered that .NET code as a CLR function but as you ar using SQL Server 2000, you will have to do something like this:

CREATE PROCEDURE SplitString
@.yourStringvarchar(100)
AS
BEGIN
DECLARE @.StringCountint, @.mycountint, @.mystrlenint
DECLARE @.myvalvarchar(100)
set @.StringCount=Len(@.yourString)
set @.mycount=1
if (CHARINDEX(',',@.yourString,1)=0)
print @.yourString
WHILE (CHARINDEX(',',@.yourString,1)<>0)
BEGIN
if @.mycount=1
set @.myval=substring(@.yourString,@.mycount,CHARINDEX('^',@.yourString,1)-1)
print @.myval
set @.yourString =substring(@.yourString,Len(@.myval)+2,Len(@.yourString))
set @.StringCount= @.StringCount -1
if (CHARINDEX(',',@.yourString,1)=0)
print ,@.yourString
END
end
GO