Separating varchar(max) string into a list in sql server

Question

I have data saved as 1¬2¬4¬5¬7 and I want to split them to first response = 1, second response = 2, third response = 4, forth response = 5 and fifth response = 7. They are in the column as varchar(max) and need to report on these. Is there a way to either to split them into 5 different rows or in a ranked list? Not all the columns have 5 parts, some have more and some less so it would be good if the answer was flexible for that.

I'm using SSRS 2008R2 and SQL Server 2008R2

Thank you in advance

Answer 1

declare @Testdata table (  Data varchar(max))
insert @Testdata select   '1¬2¬4¬5¬7'

;with tmp(DataItem, Data) as (
select   LEFT(Data, CHARINDEX('¬',Data+'¬')-1),
    STUFF(Data, 1, CHARINDEX('¬',Data+'¬'), '')
from @Testdata
union all
select   LEFT(Data, CHARINDEX('¬',Data+'¬')-1),
    STUFF(Data, 1, CHARINDEX('¬',Data+'¬'), '')
from tmp
where Data > ''
)

select * from tmp