I suspect mostly everyone is aware of using REPLACE to remove a quote from a string e.g.
DECLARE @info varchar(100) SET @info = 'Login failed for user ''abc''.' SELECT REPLACE(@info,'''','')
But, what if you’re unable to manipulate the string before using REPLACE i.e. you cannot escape the quote?
One solution is to use SET QUOTED_IDENTIFIER OFF.
SET QUOTED_IDENTIFIER OFF; DECLARE @info varchar(100) SET @info = "Login failed for user 'abc'." SELECT REPLACE(@info,'''','')