Split CSV into multiple rows

Question

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.

Sarath Subramanian · Accepted Answer · 2015-01-16 11:27:43Z

10

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, 2015 at 11:27

answered Jan 8, 2015 at 8:56

Sarath Subramanian

21.6k12 gold badges89 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nishant Over a year ago

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.

Sarath Subramanian Over a year ago

No. It is not. But many people uses that since they are unaware of the inbuilt XML function. @Nishant

Nishant Over a year ago

Its a good idea to convert regular data to a XML and work on it.

Sarath Subramanian Over a year ago

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

Sonny Childs Over a year ago

I'm digging that XML solution. I always thought it was annoying how there was no native Split() function.

|

Levin · Accepted Answer · 2012-03-21 19:29:12Z

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, 2012 at 19:29

answered Mar 21, 2012 at 19:10

Levin

2,04521 silver badges28 bronze badges

5 Comments

Cavyn VonDeylen Over a year ago

I made this change but it didn't change anything.

Levin Over a year ago

@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?

Cavyn VonDeylen Over a year ago

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.

Levin Over a year ago

@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?

Cavyn VonDeylen Over a year ago

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.

amd3 · Accepted Answer · 2022-03-16 17:58:43Z

3

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

answered Mar 16, 2022 at 17:58

amd3

8661 gold badge9 silver badges16 bronze badges

Comments

Andrey Gurinov · Accepted Answer · 2012-03-21 19:10:10Z

0

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.

answered Mar 21, 2012 at 19:10

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

Collectives™ on Stack Overflow

Split CSV into multiple rows

4 Answers 4

6 Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related