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.

Sarath Subramanian · Answer 1 · 2015-01-16T11:27:43.477

9

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

enter image description here

edited Jan 16 '15 at 11:27

answered Jan 08 '15 at 08:56

Sarath Subramanian

20,027
11
82
86

In the second method 'split' is not an inbuilt function? I see reference to split in many articles thought it was inbuilt in SQL. Thought it could be inbuilt. – Nishant Jan 16 '15 at 11:31
1

No. It is not. But many people uses that since they are unaware of the inbuilt XML function. @Nishant – Sarath Subramanian Jan 16 '15 at 11:38
Its a good idea to convert regular data to a XML and work on it. – Nishant Jan 16 '15 at 12:09
1

Yes it is. But one should be careful when the CSV has tags in it, ie <, > etc because XML will treat **<** as **&gt** and **>** as **&lt** but we can use **REPLACE** function to replace them. @Nishant – Sarath Subramanian Jan 16 '15 at 12:12
I'm digging that XML solution. I always thought it was annoying how there was no native Split() function. – Sonny Childs Jan 12 '16 at 19:40
I have solved the XML issue in another answer. I will ping you that answer after some time. That may help you. @Sonny Childs – Sarath Subramanian Jan 13 '16 at 04:33

Levin Magruder · Accepted Answer · 2012-03-21T19:29:12.570

8

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.

edited Mar 21 '12 at 19:29

answered Mar 21 '12 at 19:10

Levin Magruder

1,905
18
26

I made this change but it didn't change anything. – Cavyn VonDeylen Mar 21 '12 at 19:13
@CavynVonDeylen I edited my response, showing a session I tried it in, where works as I think you want. I'm thinking if you saved the select as a view, you have to alter the view. Or are you just typing in literally what you're showing us? – Levin Magruder Mar 21 '12 at 19:34
I pasted your code into a new query, but I got an "Incorrect syntax near ',' on the 'Vodka,Potassium,Trigo' line, and the same error as before on the cpt.interests line. I'm starting to think the issue is a little larger than my syntax, since I was having some issues doing pivots before as well. Thanks for your input though. – Cavyn VonDeylen Mar 21 '12 at 19:40
@CavynVonDeylen, I suspect some fancy pasting is doing something weird to the single quote character? If you just type at the keyboard: "select 'abc'", (without double quotes, but with single quotes) vs. if you try to paste it in, any difference? – Levin Magruder Mar 21 '12 at 19:47
I was talking with a coworker and it turns out our database is actually running Server 2005, while I'm using SSMS 2008. (I didn't realize there could be that discrepancy) This seems to be the reason for my errors. Thanks for your help. – Cavyn VonDeylen Mar 21 '12 at 19:59

score 2 · Answer 3 · answered Mar 16 '22 at 17:58

As this is old, it seems the following works in SQL Azure (as of 3/2022) The big changes being split.value instead of .data or .items as shown above; no as after the function, and lastly string_split is the method.

select Id, split.value
from #reportTmp03 rpt
cross apply string_split(SelectedProductIds, ',') split

score 0 · Answer 4 · answered Mar 21 '12 at 19:10

Try this:

--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 must use table alias instead of table name as soon as you define it.

SQL Server split CSV into multiple rows

4 Answers4

Linked

Related