Tuesday, March 29, 2011

Comma Seprator in SQL


create table Employee_Names (Names nvarchar(100))

insert into employee_names values ('Dinesh,Manoj,Kumar,Radha,Dhiraj,Saraswathi')
insert into employee_names values ('Judu,Anoop,Srinivasan')



Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255)

declare @Names nvarchar(max)

Declare Test_Cursor Cursor for
select names from employee_names where names like '%,%'

open Test_Cursor
Fetch NEXT from Test_Cursor INTO @Names
While @@fetch_status=0
Begin

Select @prevloc=0,@loc=1,@cnt=1,@person = @Names
While @loc != 0
Begin
    set @prevloc=(case when @loc = 1 then 0 else @loc end) +1
    set @loc = charindex(',',@person,@loc+1)
    Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end))
    print 'String = ' + @Str
--select   @Str

End  
Fetch NEXT from Test_Cursor INTO @Names

End
Close Test_Cursor
Deallocate Test_Cursor

No comments:

Post a Comment