Raymond Lewallen

Sponsors

The Lounge

Syndication

Recent Posts

Tags

View more

News

About Me

MVP

Archives

Advertisement

Raymond Lewallen » Quick T-Sql to parse a delimited string
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
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

Posted 10-26-2005 8:48 AM by Raymond Lewallen
Filed under:

[Advertisement]

Comments

David Brabant wrote re: Quick T-Sql to parse a delimited string
on 10-26-2005 1:58 PM
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
Raymond Lewallen wrote re: Quick T-Sql to parse a delimited string
on 10-26-2005 3:21 PM
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'.
Oskar Austegard wrote re: Quick T-Sql to parse a delimited string
on 10-28-2005 6:29 PM
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
Roger's Blog wrote How to write a keyword search procedure without doing something crazy
on 11-21-2005 12:36 AM
Roger's Blog wrote How to write a keyword search procedure without using sql exec()
on 12-10-2005 2:00 PM
chris wrote re: Quick T-Sql to parse a delimited string
on 04-17-2006 11:49 AM
old post, but i just stole your code. works perfectly - patindex or not.
thorkyl wrote re: Quick T-Sql to parse a delimited string
on 06-06-2006 9:51 AM
Thanks, just what I needed....
John D wrote re: Quick T-Sql to parse a delimited string
on 12-26-2006 4:56 PM

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

steve wrote re: Quick T-Sql to parse a delimited string
on 03-15-2007 7:21 AM

Thanks, this worked for me to parse my postcodes.

Bruce H wrote re: Quick T-Sql to parse a delimited string
on 04-11-2007 6:20 PM

Thank you.

Just what I was looking for.

Great job.

B

neven wrote re: Quick T-Sql to parse a delimited string
on 11-21-2007 7:47 PM

Many thanks!

Tripod wrote re: Quick T-Sql to parse a delimited string
on 11-28-2007 10:59 AM

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!

Tripod wrote re: Quick T-Sql to parse a delimited string
on 11-28-2007 11:35 AM

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!

MikeJohnson wrote re: Quick T-Sql to parse a delimited string
on 12-26-2007 8:36 PM

Good stuff.

Thanks!

Rod wrote re: Quick T-Sql to parse a delimited string
on 01-10-2008 12:00 AM

Thanks.

Raj wrote re: Quick T-Sql to parse a delimited string
on 02-21-2008 3:57 PM

Thanks. Worked for me.

Ryan wrote re: Quick T-Sql to parse a delimited string
on 03-30-2008 11:01 PM

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!

to parse wrote to parse
on 05-17-2008 4:45 PM

Pingback from  to parse

Babak Mokri wrote re: Quick T-Sql to parse a delimited string
on 07-03-2008 7:16 AM

Thank you very much it work just fine.

Sebastian wrote re: Quick T-Sql to parse a delimited string
on 02-09-2009 2:31 PM

i took some of your code and i made my own function i hope somebady like it.

CREATE FUNCTION Split(

@IDValues as varchar(500),

@Delimeter as varchar(1)

)

RETURNS @tValues TABLE(IDAux INT)

AS

BEGIN

DECLARE @I AS INT, @Aux AS INT

SET @I = 1

SET @Aux = 1

WHILE @I <= LEN(@IDValues)

BEGIN

IF SUBSTRING(@IDValues,@I,1)= @Delimeter

   BEGIN

INSERT INTO @tValues (IDAux) VALUES (CAST(SUBSTRING(@IDValues,@Aux,(@I-@Aux))AS INT))

SET @Aux = @i + 1

END

SET @i = @i + 1

END

RETURN

END

GO

--examples with differents delimeters

select * from Split('1058,1059,1060,1062,',',')

select * from Split('1058|1059|1060|1062|','|')

select * from Split('1058;1059;1060;1062;',';')

pass param array type arg? | keyongtech wrote pass param array type arg? | keyongtech
on 03-04-2009 12:08 PM

Pingback from  pass param array type arg? | keyongtech

Raj wrote re: Quick T-Sql to parse a delimited string
on 03-05-2009 11:45 AM

Raymond -- Thanks!

This is just what I was looking for; easy to understand, and effective.

Since I'll be using this for Sybase ASE 12.5, the only change I might have to make is:

From:  set @string = stuff(@string, 1, @pos, '')

To:  set @string = stuff(@string, 1, @pos, null)

Per Sybase 12.5:

"Adaptive Server converts an empty string constant to a string of 1 space"

Thanks again!

Raj

humair wrote re: Quick T-Sql to parse a delimited string
on 03-13-2009 10:59 AM

thanks alot this worked for me

Mike Maholchic wrote re: Quick T-Sql to parse a delimited string
on 05-01-2009 9:52 AM

Just what I needed to get the file name from a filename and path, thanks

Add a Comment

(required)  
(optional)
(required)  
Remember Me?