?
Solved

Split comma separated values in column

Posted on 2013-01-28
12
Medium Priority
?
933 Views
Last Modified: 2013-04-03
I have a column in a table that can have anywhere from 1 to 6 comma separated values:
column1 = value1,value2, value3, etc

I need to parse that column so that the resulting select returns a row with a single column for each delimited value.

Thanks in advance!
0
Comment
Question by:Rickzzz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 38829592
try like this,
create a UDF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetListFromCSVString]
(		
	@csvString varchar(500), 
	@sep varchar(1)
)
RETURNS @ValueList TABLE 
(
	ID int,
	FldVal varchar(50)
)
AS
begin
	declare @curPos int
	declare @prevCurPos int
	declare @ID int

	set @ID=1
	set @curPos = -1
	set @prevCurPos = 1
	
	While @curPos <>0
	begin
		set @curPos = charindex(@sep, @csvString, @curPos + 1)
		if @curPos <> 0 
		begin
			insert into @ValueList values( @ID, substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
		end
		else
		begin
			insert into @ValueList values(@ID, substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPos +1) )
		end
		Set @ID= @ID+1
		Set @prevCurPos = @curPos+1
	end
	return 

End 

Open in new window


use it your SQL as follows

-- use with CSV string as parameter
select * from  [dbo].[GetListFromCSVString]('Test1,Test2',',')

-- use with CSV values from a table
select  yourtableName.*  , A.FldVal from  yourtableName
outer apply [dbo].[GetListFromCSVString]( yourtableName.csvFieldName ,',') A
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 1000 total points
ID: 38830205
Here's a method without using a custom function:

declare @theString varchar(100) = 'value1,value2,value3,last value';
declare @delimiter varchar(10) = ',';

with TheData as ( select @theString StrVal)
, StrToCols as (
	select ltrim(SUBSTRING(@theString, number
			, CHARINDEX(@delimiter, D.StrVal + @delimiter,number) - number)) AS val
		, ROW_NUMBER() over (order by N.number) as ID
	FROM TheData D
	CROSS JOIN (SELECT number FROM master..spt_values with (nolock) WHERE type = 'P') AS N
	WHERE SUBSTRING(@delimiter + D.StrVal, number, 1) = @delimiter
		AND number < LEN(D.StrVal) + 1
)
select *
from StrToCols
pivot (min(val) for ID in ([1], [2], [3], [4], [5], [6])) as pvt

Open in new window

For info on what I'm doing here, I'd like to refer you to the following article by our expert mark_wills: Fun with MS SQL spt_values for delimited strings and virtual calendars

I'm using his method to split the string, then I use the ROW_NUMBER function to add an incrementing identifier to the values and finally I use the PIVOT operator to make columns out of the rows.
0
 

Author Comment

by:Rickzzz
ID: 38831124
Thanks guys.

Appari, I created the UDF and I get this:
"Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'auto'."

when I run:
select
	[auto].*
	,a.FldVal
from 
	[auto]
		outer apply
			dbo.GetListFromCSVString([auto].vehicleList,',') a  

Open in new window


Do you see anything wrong here?  Thanks!
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 39

Expert Comment

by:appari
ID: 38833396
what happens if you run the following sql? are you getting the splitted values?

select * from  [dbo].[GetListFromCSVString]('Test1,Test2',',')
0
 

Author Comment

by:Rickzzz
ID: 38833414
That returns:

ID      FldVal
1      Test1
2      Test2

Thanks.
0
 
LVL 39

Expert Comment

by:appari
ID: 38833445
i dont see any problem with the SQL you were executing. try this

select      A.*
      ,B.FldVal
from  [auto] A  outer apply dbo.GetListFromCSVString(A.vehicleList,',') B
0
 

Author Comment

by:Rickzzz
ID: 38833521
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

I had a similar issue running a udf from another post. Might just be a default env setting or something on my end jacking things up.

Anyway, like the other post, your certs and points on this site pretty much dictate your stuff is more than sound. Looks like I'll have to Google for the obscure reason I can't execute the thing. Something to do with the table name [Auto] maybe?

I'll go ahead and mark this as the answer, again, given your status here. Thanks for the extra effort though.
0
 
LVL 39

Expert Comment

by:appari
ID: 38833538
what is your sqlserver version?
0
 

Author Comment

by:Rickzzz
ID: 38833692
2012.

Rick
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38834445
Hi Rick, did you give my proposal a try as well?
0
 

Author Comment

by:Rickzzz
ID: 38836904
Not yet Valentino, my head wants to explode when I try to make sense of it. But I'm going to. Thanks.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38836935
Heh, I understand :)

If you have any questions on that solution, don't hesitate to ask!

In short, all you need to do is replace this:

select @theString StrVal

with your query that retrieves the column with the comma-separated values.  It should run fine, you'll always get six columns back.  In case your column contains less than six values, let's say four, the last two columns that get generated will be null.

To get a good understanding of it, try it out with a couple of examples first, like I did in my proposal.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question