CODEWITHSUNDEEP
sqlsql-server
Cavyn VonDeylen
Cavyn VonDeylen

Reputation: 4249

Split CSV into multiple rows

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.

Upvotes: 6

Views: 24494

Answers (4)

amd3
amd3

Reputation: 846

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

Upvotes: 3

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

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)

METHOD 2 : Using function dbo.Split

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

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

Upvotes: 10

Levin
Levin

Reputation: 2015

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.

Upvotes: 8

Andrey Gurinov
Andrey Gurinov

Reputation: 2895

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.

Upvotes: 0

Related Questions