DECLARE @NextPos INT, @LastPos INT ,@colcount int=0,@sql varchar(1000)
truncate table Splitresult
SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0
WHILE @NextPos > 0
BEGIN
IF exists( select 1 from Splitresult where id=@id)
begin
set @colcount=@colcount+1
set @sql='update Splitresultset column'+ltrim(rtrim(str(@colcount)))+'=''' + SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1) +''' where id=' + ltrim(rtrim(str(@id)))
EXEC( @sql)
end
else
begin
set @colcount=@colcount+1
set @sql='insert into Splitresult(id,column'+ltrim(rtrim(str(@colcount)))+') select ' + ltrim(rtrim(str(@id)))+',''' + SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)+''''
EXEC (@sql)
end
SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END
IF @NextPos <= @LastPos
IF exists( select 1 from Splitresultwhere id=@id)
begin
set @colcount=@colcount+1
set @sql='update Splitresultset column'+ltrim(rtrim(str(@colcount)))+'=''' + SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos) +''' where id=' + ltrim(rtrim(str(@id)))
EXEC( @sql)
End
else
Begin
set @colcount=@colcount+1
set @sql='insert into Splitresult(id,column'+ltrim(rtrim(str(@colcount)))+') select ' + ltrim(rtrim(str(@id)))+',''' + SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos) +''''
EXEC (@sql)
end