Hi,

try this statement

create table #temp

(

slno int identity(1,1),

name1 varchar(100)

)

declare @abc varchar(1000)/*Alwayes should be in max value*/

select @abc = '1,joy~2,jack~2,rozy~3,X~4,YYY~5'

select @abc = 'select ''' + replace (@ABC,',',''' union select ''')+''''

insert into #temp (name1)

exec (@ABC)

select * from #temp

RESULT

slnoname1

11

2jack~2

3joy~2

4rozy~3

5X~4

6YYY~5

for removing the '~'

update #temp

set name1 = left(name1,charindex('~',name1,0)-1) from #temp

where name1 like '%~%'

select * from #temp

slnoname1

11

2jack

3joy

4rozy

5X

6YYY

ARUN SAS