Solved

Split comma separated values in column

Posted on 2013-01-28
12
903 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
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 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

831 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