SQL Server splits CSV into multiple rows

Question

I understand that this question has been asked before, but I can’t get it to work for some reason.

I am using the split function from this SQL Team thread (second record) and the following queries.

--This query converts the interests field from text to varchar select cp.id ,cast(cp.interests as varchar(100)) as interests into #client_profile_temp from client_profile cp --This query is supposed to split the csv ("Golf","food") into multiple rows select cpt.id ,split.data from #client_profile_temp cpt cross apply dbo.split( cpt.interests, ',') as split <--Error is on this line 

However i get

 Incorrect syntax near '.' 

where I noted above.

In the end I want

 ID INTERESTS 000CT00002UA "Golf","food" 

to be

 ID INTERESTS 000CT00002UA "Golf" 000CT00002UA "food" 

I am using SQL Server 2008 and am basing my answer on this StackOverflow question . I am new to SQL, so any other words of wisdom will be appreciated as well.

Answer 1

 from #client_profile_temp cpt cross apply dbo.split( #client_profile_temp.interests, ',') as split <--Error is on this line 

I think that explicitly giving the name #client_profile_temp after you gave it an alias is a problem, try making this last line:

  cpt.interests, ',') as split <--Error is on this line 

EDIT You say

I made this change and changed nothing

Try pasting the code below (in a new SSMS window)

 create table #client_profile_temp (id int, interests varchar(500)) insert into #client_profile_temp values (5, 'Vodka,Potassium,Trigo'), (6, 'Mazda,Boeing,Alcoa') select cpt.id ,split.data from #client_profile_temp cpt cross apply dbo.split(cpt.interests, ',') as split 

See if it works as you expect; I am using SQL Server 2008 and it works for me to get the results that I think you need.

If you say, “I made the changes,” did you just change the stored procedure but not run it, or change the script that creates the stored procedure and not run it, something like this line? Like I said, this works for me.

Answer 2

Table

 x-----------------x--------------------x | ID | INTERESTS | x-----------------x--------------------x | 000CT00002UA | Golf,food | | 000CT12303CB | Cricket,Bat | x------x----------x--------------------x 

METHOD 1: Using the XML Format

 SELECT ID,Split.a.value('.', 'VARCHAR(100)') 'INTERESTS' FROM ( -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one SELECT ID, CAST ('<M>' + REPLACE(INTERESTS, ',', '</M><M>') + '</M>' AS XML) AS Data FROM TEMP ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) 

• SQL FIDDLE

METHOD 2: Using the dbo.Split

 SELECT a.ID, b.items FROM #TEMP a CROSS APPLY dbo.Split(a.INTERESTS, ',') b 

• SQL FIDDLE

Here's the dbo.Split function.

 CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end 

FINAL RESULT

Answer 3

Try the following:

 --This query is supposed to split the csv ("Golf","food") into multiple rows select cpt.id ,split.data from #client_profile_temp cpt cross apply dbo.split(cpt.interests, ',') as split <--Error is on this line 

You should use the table alias instead of the table name as soon as you define it.