Yeah! very nice, kkeeffe...always appreciate someone using "While" loops instead of cursors. Below is your code modified to:

1) Remove @Rowcount variable and use "IF @@Rowcount = 0 Break" instead. It's cheaper.

2) Removed Delete statement and added Where clause "Where Len(Name_) <> 0". Also cheaper, as it's checking a condition instead of modifying a row.

3) added "If object_id('tempdb..#table1') is not null Drop Table #table1". Would also suggest changing these tables to "Declare @Table1 Table (Number int, Name_ varchar(50))" if your version allows.

These modifications may seem trivial, but since it's a loop they can mean significant performance gains. Especially when compared to a cursor.

If object_id('tempdb..#table1') is not null Drop Table #table1

Create Table #table1 (Number int, Name_ varchar(50))

Insert #table1 Values (1, 'ee,ed,ff,er,fsd')

Insert #table1 Values (2, 'rr,kk')

If object_id('tempdb..#table2') is not null Drop Table #table2

Create Table #table2 (Number int, Name_ varchar(10))

While 1 = 1 Begin

Insert Into #table2

Select t1.Number

,

Case

When Charindex(',', t1.Name_) > 0

Then Replace(Left(t1.Name_, (Charindex(',', t1.Name_))), ',', '')

Else t1.Name_

End

From #table1 t1

Where Len(Name_) <> 0

IF @@Rowcount = 0 Break

Update #table1

Set Name_ =

Case When Charindex(',', Name_) > 0

Then Right(Name_, Len(Name_) - Charindex(',', Name_))

Else ''

End

From #table1

Where Len(Name_) <> 0

End

Select *

From #table2