in Search

Do you Twitter? Follow us @CodeBetter

Raymond Lewallen

Framework Design, Agile Coach, President Oklahoma City Developers Group, Microsoft MVP C#, TDD, Continuous Integration, Patterns and Practices, Domain Driven Design, Speaker, VB.Net, C# and Sql Server

Quick T-Sql to parse a delimited string

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:

Parse a comma-delimeted string

-- This would be the input parameter of the stored procedure, if you want to do it that way, or 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

Published Oct 26 2005, 08:48 AM by Raymond Lewallen

Filed under: Sql Development

Comments

David Brabant said:

Hi Raymond,

Don`t want to be picky here (or do I ? :-), but is there any reason why you use patindex?

This works too:

-- [...]

set @pos = charindex(',' , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos - 1)
print cast(@piece as varchar(500))
set @string = stuff(@string, 1, @pos, '')
set @pos = charindex(',' , @string)
end

# October 26, 2005 1:58 PM

Raymond Lewallen said:

David,

I'm just partial to using PATINDEX because I'm used to using wildcard characters in my search strings, which CHARINDEX does not support. You're way works just fine too in this exact scenario.

However, with PATINDEX, you can use the delimeter of ",_,", which would exclude single char pieces of data from your result set. You cannot do this with CHARINDEX.

This following piece of code shows the value of using PATINDEX

declare @a varchar(100)
set @a = 'abcdefgh,ijklm,n,opqrs,tuv'
print patindex('%,_,%',@a)

Will print '15'.

# October 26, 2005 3:21 PM

Oskar Austegard said:

You may also want to have a look at Erland Sommarskog's article at http://www.sommarskog.se/arrays-in-sql.html.

I took his code and tweaked it some, dbo.Numbers is a simple standalone table with numbers from 1 to 1 million.

--Fast Split function using a sequence table (dbo.Numbers)
--Created 08/29/05 by Oskar Austegard from Erland Sommarskog's code at
--http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
CREATE FUNCTION Split (
@List varchar(7998), --The delimited list
@Del char(1) = ',' --The delimiter
)
RETURNS @T TABLE (Item varchar(7998))
AS
BEGIN
DECLARE @WrappedList varchar(8000), @MaxItems int
SELECT @WrappedList = @Del + @List + @Del, @MaxItems = LEN(@List)

INSERT INTO @T (Item)
SELECT SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del, @WrappedList, Number + 1) - Number - 1)
FROM dbo.Numbers n
WHERE n.Number <= LEN(@WrappedList) - 1
AND SUBSTRING(@WrappedList, n.Number, 1) = @Del

RETURN
END

# October 28, 2005 6:29 PM

Roger's Blog said:

# November 21, 2005 12:36 AM

Roger's Blog said:

# December 10, 2005 2:00 PM

chris said:

old post, but i just stole your code. works perfectly - patindex or not.

# April 17, 2006 11:49 AM

thorkyl said:

Thanks, just what I needed....

# June 6, 2006 9:51 AM

John D said:

...and just what I needed as well. Thanks!

# December 26, 2006 4:56 PM

steve said:

Thanks, this worked for me to parse my postcodes.

# March 15, 2007 7:21 AM

Bruce H said:

Thank you.

Just what I was looking for.

Great job.

# April 11, 2007 6:20 PM

neven said:

Many thanks!

# November 21, 2007 7:47 PM

Tripod said:

Please provide an example of how one would use this function to set variables for each of the parsed values. For example, if the string to parse was "ABC-123-DEF" how would we get the values into their own variables? For my requirements, there will be a constant maximum number of parts to parse from the string; not infinite. That value is 4 and the logic should handle these string examples:

(null - no value passed)

ABC

ABC-123

ABC-123-DEC

ABC-123-DEC-XYZ

Thanks in advance!

# November 28, 2007 10:59 AM

Tripod said:

Great stuff but now I need to get the pased results into their own variables. Of course, I don't have an infinite number of parts in the string-to-parse. Here are six example strings to be parsed that has 0 - 5 parts:

1. null

2. ABC

3. ABC-123

4. ABC-123-DEF

5. ABC-123-DEF-789

6. ABC-123-DEF-789-XYZ

I would like to get the results into variables such as:

@v_parsed_1

@v_parsed_2

@v_parsed_3

@v_parsed_4

@v_parsed_5

where, in the 4th example above, would result in:

@v_parsed_1 = 'ABC'

@v_parsed_2 = '123'

@v_parsed_3 = 'DEF'

@v_parsed_4 = null

@v_parsed_5 = null

Thanks in advance!

# November 28, 2007 11:35 AM

MikeJohnson said:

Good stuff.

Thanks!

# December 26, 2007 8:36 PM

Rod said:

Thanks.

# January 10, 2008 12:00 AM

Raj said:

Thanks. Worked for me.

# February 21, 2008 3:57 PM

Ryan said:

Thanks. I had code that I wrote in the past that did this very thing, but I was in a crunch and didn't have time to either find my old code or write it again from scratch. Thanks for saving me the time!

# March 30, 2008 11:01 PM

to parse said:

Pingback from to parse

# May 17, 2008 4:45 PM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

About Raymond Lewallen

Working primarily in the public sector during his career, Raymond has designed and built several high profile enterprise level applications for all levels of the government. Raymond now works as a solutions architect for EMC. Raymond is an agile coach, Microsoft MVP C# and also president of the Oklahoma City Developers Group and Oklahoma Agile Developers Group. Raymond spends a lot of his time learning and teaching such things as Test Driven Development, Domain Driven Design, Design Patterns and Extreme Programming practices and principles, to name a few. Raymond is also an advocate of Alt.Net. Raymond is primarily a framework guy, so don't ask him anything about UI :)

About Me

Raymond Lewallen

Quick T-Sql to parse a delimited string

Comments

David Brabant said:

Raymond Lewallen said:

Oskar Austegard said:

Roger's Blog said:

Roger's Blog said:

chris said:

thorkyl said:

John D said:

steve said:

Bruce H said:

neven said:

Tripod said:

Tripod said:

MikeJohnson said:

Rod said:

Raj said:

Ryan said:

to parse said:

Leave a Comment

About Raymond Lewallen

This Blog

Syndication

Recent Posts

Tags

News

MVP

Archives