Solved

Split comma separated values in column

Posted on 2013-01-28
12
908 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

696 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