Comma Seperated Value seperation

Question

Comma Seperated Value seperation

Manokarnan

SSC Eights!

Points: 852
More actions
March 16, 2006 at 10:45 pm

#91607

Hi,
I have a table in that one column had a comma seperated value
ex: M1,M2,M3,M4
I need a result like i mentioned below
M1
M2
M3
M4
I need a solution how i separate the comma seperated value and get the result as mentioned above?
manokarnan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

is this for a single excercise (like a data load) and won't do agin (hopefully) or is it something that you ned to build into a procedure for constant re-use?

MVDBA

RyanRandall SSChampion Points: 13623 More actions · Answer 2

If I had a penny for how many times this is requested...

--This SQL script is safe to run

declare @t table (id int identity(1, 1), v varchar(50))

insert @t

select 'M1,M2,M3,M4'

union all select 'M1,M2,M3'

union all select 'N1,N2,N3,N4'

union all select 'N10,N12'

declare @Numbers table (i int identity(1, 1), j bit)

insert into @Numbers

select top 50 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select t.id, left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v

from @t t, @Numbers

where substring(','+v, i, 1) = ','

order by t.id, v

(...I would have £12.37)

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

or this script

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=319

personally if it's data minipulation as a one off excel (spit!) has 2 functions - deleimeter to column - column to row - these can be used to quickly handball data for one off situations

MVDBA

Manokarnan SSC Eights! Points: 852 More actions · Answer 4

Thanks for all you replies. It's not a single time query i want to use this concept inside the stored procedure.

ManoKarnan

bazaleel Newbie Points: 1 More actions · Answer 5

Hi

I am having a situation like this

sl Field

1 Text1,Text5

2 Text1,Text2,Texte5

3 Text5

4 Text3, Text4, Text1

5 Text4

6 Text2, Text 3

and I want

Text1

Text2

Text3

Text4

Text5

Distinct from Comma seperated field

can some one please help me on this

Thanks and Regards

Selwyn

RyanRandall SSChampion Points: 13623 More actions · Answer 6

One option (of many) is to simply adapt the above (by adding a distinct and removing the ids):

declare @t table (id int identity(1, 1), v varchar(50))

insert @t

select 'M1,M2,M3,M4'

union all select 'M1,M2,M3'

union all select 'N1,N2,N3,N4'

union all select 'N10,N12'

declare @Numbers table (i int identity(1, 1), j bit)

insert into @Numbers

select top 50 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select distinct left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v

from @t t, @Numbers

where substring(','+v, i, 1) = ','

order by v

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.