Check this.
am inserting into temptable. If you want to directly update it to your physical table , have the number column as identity column
My stored proc:
Create PROCEDURE asp_splitstrings
(@string varchar(50))
AS
DECLARE @id integer = 1,
@previous_id integer = 0,
@value varchar(50)
create table #temp
(Number int Identity(1,1),
IntegerFromList varchar(50))
WHILE @id > 0
BEGIN
SET @id = CHARINDEX(',',@string,@previous_id+1)
IF @id > 0
BEGIN
SET @value = SUBSTRING(@string,@previous_id+1,@id-@previous_id-1)
INSERT INTO #temp VALUES (@value)
SET @previous_id = @id
END
END
IF @previous_id < LEN(@string)
BEGIN
SET @value = SUBSTRING(@string,@previous_id+1,LEN(@string))
INSERT INTO #temp VALUES (@value)
END
select * from #temp
Execute sp now
exec asp_splitstrings 'CHOPL,NAKRL,NLGD2,DEVKD,HALYA'