Solved

Split comma separated values in column

Posted on 2013-01-28
12
881 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
Comment Utility
That returns:

ID      FldVal
1      Test1
2      Test2

Thanks.
0
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Rickzzz
Comment Utility
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
Comment Utility
what is your sqlserver version?
0
 

Author Comment

by:Rickzzz
Comment Utility
2012.

Rick
0
 
LVL 37

Expert Comment

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

Author Comment

by:Rickzzz
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now