sql server function stuff before and after the string

To append  a string at each comma

Example for the string 'A,B,C'.

Result should be 'beforestringAafterstringbeforestringBafterstringbeforestringCafterstring

when you need these operations ?

Mostly during automations , for example if you are retrieving the columns from the sys.columns table to generate a generic select statement with casting as below example

column names output from the syscolumns 'IID,Name,Age'

output : CAST(IID AS VARCHAR(MAX)), CAST(Name AS VARCHAR(MAX)), CAST(Age AS VARCHAR(MAX))

execute the script to create the function as below and run the below statement

select dbo.fn_stuff('xx_,xxxx,',',','before','after')

create  function dbo.fn_stuff(@vString varchar(4000),@exp varchar(5),@vStart varchar(2000),@vEnd varchar(2000)) returns varchar(8000) as begin declare @vRet varchar(8000) SELECT @vRet= coalesce(nullif(cast(charindex(@vString,@exp) as varchar(max)),0), STUFF(replace(@exp+@vString,@exp,@vEnd+@vStart) + @vEnd , 1,len(@vEnd),'')) return @vRet end