CODEWITHSUNDEEP
sql-server-2005
Abhishek
Abhishek

Reputation: 191

separating values of a single column in sqlserver

I am having a field named categoryid in a question table which can have multiple values inputted into it for showing different categories. now i want to separate each value and get it individually.

the values are separated by , character

for example

categoryid varchar
3
3,8
5,9,7
8,5,7,3,2

How can i separate each of them using sql queries? please help

Upvotes: 0

Views: 110

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

declare @T table
(
  category_id varchar(10)
)

insert into @T
select '3' union all
select '3,8' union all
select '5,9,7' union all
select '8,5,7,3,2'

;with C(ID, category_id) as
(
  select cast(left(category_id, charindex(',', category_id+',', 1)-1) as int),
         stuff(category_id, 1, charindex(',', category_id+',', 1), '')
  from @T
  union all
  select cast(left(category_id, charindex(',', category_id+',', 1)-1) as int),
         stuff(category_id, 1, charindex(',', category_id+',', 1), '')
  from C
  where len(category_id) > 0
)
select ID
from C

Upvotes: 1

Related Questions