Click here to Skip to main content
15,117,549 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
My data is something like this..'111','bbb','333',......'zzz'

How do i display it in this way..Output:-
111
bbb
333
...
..
...
zzz
Posted
Updated 22-Jul-11 0:49am
v2

1 solution

create a function

SQL
Create FUNCTION dbo.testSplit(@String varchar(8000))
returns @temptable TABLE (items varchar(255))
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(',',@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(CONVERT(varchar(255), @slice))

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end



and call it like that

select * from dbo.testSplit('''111'',''bbb'',''333''')


see the ref here[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900