Replace Last Charecter of the String in SQL Server

In the string if we need to replace the last character to some new character then below script will help you.

 

DECLARE @string VARCHAR(max)='a,b,c,d'

DECLARE @LastCharachter VARCHAR(2)=',',

@NewCharachter VARCHAR(2)=' &';

 

select CASE WHEN CHARINDEX(@LastCharachter,REVERSE(@string)) <> 0 then (SELECT (SUBSTRING(@string,0,LEN(@string) - CHARINDEX(@LastCharachter,REVERSE(@string))+1))

+@NewCharachter+ SUBSTRING(@string,LEN(@string) - CHARINDEX(@LastCharachter,REVERSE(@string))+2,LEN(@string)) )

ELSE @string

end

--Input=a,b,c,a
Output =a,b,c&d