DECLARE @Numbertable table

(

ID INT PRIMARY KEY

)

INSERT INTO @Numbertable

select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)

from sys.objects si,sys.objects s

DECLARE @separatolog TABLE

(

SeparatorLogID INT,

ID INT IDENTITY(1,1) PRIMARY KEY

)

DECLARE @STR varchar(100) = 'FN,SP,TBL,091.8891'

DECLARE @Separator VARCHAR(10) = ','

DECLARE @ValueAdd VARCHAR(10) = '80990'

DECLARE @Action VARCHAR(10) = '3'--1. P..PostFix 2. R..Prefix 3.Number for postion.

IF (ISNUMERIC(@Action) =0)

BEGIN

SELECT @STR =

STUFF((

SELECT

CASE WHEN @Action = 'R' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END

+

ISNULL(@Separator+@Str,'')

+

CASE WHEN @Action = 'P' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END

),1,1,'')

END

ELSE

BEGIN

INSERT INTO @separatolog

select distinct CHARINDEX(',',@str,ID)

from @Numbertable

where ID <= LEN(@str) and CHARINDEX(',',@str,ID) > 0

ORDER BY 1

DECLARE @CurrentSepartorID INT

SET @CurrentSepartorID = isnull((select SeparatorLogID

from @separatolog where ID = CAST(@Action AS INT)-1),CASE WHEN @Action = 1 THEN 0 ELSE LEN(@str)+1 END)

select @STR = STUFF(SUBSTRING(ISNULL(@Separator+@str,''),1,@CurrentSepartorID)

+ISNULL(@Separator+@ValueAdd,'')

+ISNULL(@Separator+NULLIF(SUBSTRING(@str,@CurrentSepartorID+1,LEN(@str)),''),''),1,1,'')

END

select @STR