Another method to remove a quote from a string

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,'''','')

image1

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,'''','')

image2

Leave a Reply