CSV issues in SQL---need help

Question

I have this Split function which i got it from some site, now my problem is I am having one table where I want to push in imagespath. For now user can pass two images but in future there might be rise in the number of images required so we have used comma separated column which is a bad practice but was required in this case. Now when retrieve this images I want to segregate those images based on their ids

in database

1  abc.png,xyz.png
2  fgvm.png,ghy.jpg

like I want

Imagepath    ID
abc.png      1
xyz.png      1
fgvm.png     2
ghy.jpg      2

Using in the split function I'm doing this

 select * from dbo.split ((SELECT ImagePath
    FROM  dbo.AdvtManagement(nolock)),',')

But this query would give me like this

id val
1  abc.png
2  xyz.png   
3  fgvm.png
4  ghy.jpg

Can somebody help me with what I want?

split code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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

we'll need the code for the split() function that you are using — codeulike
– codeulike, Commented May 3, 2011 at 13:33

Mikael Eriksson · Accepted Answer · 2011-05-03 13:57:46Z

1

select A.ID, S.Items
from AdvtManagement as A
  cross apply
    dbo.split(A.ImagePath, ',') as S

Same code with some test data

with AdvtManagement(ID, ImagePath) as
(
  select 1, 'abc.png,xyz.png' union all 
  select 2, 'fgvm.png,ghy.jpg' 
)

select A.ID, S.Items
from AdvtManagement as A
  cross apply dbo.split(A.ImagePath, ',') as S

Result:

ID  Items
--  -------
1   abc.png
1   xyz.png
2   fgvm.png
2   ghy.jpg

edited May 3, 2011 at 13:57

answered May 3, 2011 at 13:40

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

5 Comments

Mikael Eriksson Over a year ago

@user - That is strange. S is not a column, it is an alias for the table returned by the split function. What does your statement look like?

MatBailie Over a year ago

@user - what version of SQL are you using?

MatBailie Over a year ago

@user - I agree with Mikael; can you show us the exact code you are using to get your error message? Mikael's answer should work, perhaps there is a typo somewhere?

abhijit Over a year ago

i got my answer the query should be this way SELECT P.ID,ST.val FROM AdvtManagement AS P CROSS APPLY dbo.SPLIT(P.ImagePath,',') AS ST; Thanks Mikael

abhijit Over a year ago

it should be S.val or S.* not s.items

Collectives™ on Stack Overflow

CSV issues in SQL---need help

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related