This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Quick T-Sql to parse a delimited string Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

I was working on some code to parse some data for a friend, and had to write this little piece of code that some of you might find useful.  Right now, you can just take this, pop it into query analyzer and run it.  Its simple enough to change this to a function that accepts the string, plus a delimeter value, such as:

CREATE FUNCTION dbo.ParseString (@string varchar(500), @delimeter char(1))
RETURNS table

Or do the same type thing using a stored procedure, whatever floats your boat.  You'll juse have to make some small modifications to the code.

Here is the code:

-- This would be the input parameter of the stored procedure, if you want to do it that way, of a UDF
declare @string varchar(500)
set @string = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

declare @pos int
declare @piece varchar(500)

-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@string),1) <> ','
 set @string = @string  + ','

set @pos =  patindex('%,%' , @string)
while @pos <> 0
begin
 set @piece = left(@string, @pos - 1)
 
 -- You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @string = stuff(@string, 1, @pos, '')
 set @pos =  patindex('%,%' , @string)
end

The above code outputs:

ABC
DEF
GHIJK
LMNOPQRS
T
UV
WXY
Z