SQL Server split CSV into multiple rows

Question

I realize this question has been asked before, but I can't get it to work for some reason.

I'm using the split function from this SQL Team thread (second post) 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'm getting an

Incorrect syntax near '.'

error where I've marked above.

In the end, I want

ID              INTERESTS
000CT00002UA    "Golf","food"

to be

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

I'm using SQL Server 2008 and basing my answer on this StackOverflow question. I'm fairly new to SQL so any other words of wisdom would be appreciated as well.

Answer 1

TABLE

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

METHOD 1 : Using 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 function dbo.Split

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

• SQL FIDDLE

And dbo.Split function is here.

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 2

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

I think the explicit naming of #client_profile_temp after you gave it an alias is a problem, try making that last line:

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

EDIT You say

I made this change and it didn't change anything

Try pasting the code below (into 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'm using sql server 2008 and that works for me to get the kind of results I think you want.

Any chance when you say "I made the change", you just changed a stored procedure but haven't run it, or changed a script that creates a stored procedure, and haven't run that, something along those lines? As I say, it seems to work for me.