Solved

Split comma separated values in column

Posted on 2013-01-28
12
892 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IN with @variable 5 25
Convert SP in a format for debugging 7 30
SqlAdvisor 2016 3 29
MS SQL - Rotating Values in SQL 9 55
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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 video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

27 Experts available now in Live!

Get 1:1 Help Now