Replace single quotes in SQL Server

Question

I have this function in SQL Server to replace single quotes.

But when I insert a single quote it throws an error on Replace(@strip,''','')):

Create Function [dbo].[fn_stripsingleQuote]
    (@strStrip varchar(Max))
    returns varchar
as
begin
    declare @CleanString varchar(Max)
    SET @var=(Replace(@strip,'',''))

    return @var
end

Answer 1

Try this :

select replace (colname, char(39)+char(39), '') AS colname FROM .[dbo].[Db Name];

I have achieved the desired result. Example : Input value --> Like '%Pat') '' OR

      Want Output -->  *Like '%Pat') OR*

using above query achieved the desired result.

Answer 2

The striping/replacement/scaping of single quotes from user input (input sanitation), has to be done before the SQL statement reaches the database.

Answer 3

Try REPLACE(@strip,'''','')

SQL uses two quotes to represent one in a string.

Answer 4

I think this is the shortest SQL statement for that:

CREATE FUNCTION [dbo].[fn_stripsingleQuote] (@strStrip varchar(Max))
    RETURNS varchar(Max)
AS
BEGIN    
    RETURN (Replace(@strStrip ,'''',''))
END

I hope this helps!

Answer 5

Looks like you're trying to duplicate the QUOTENAME functionality. This built-in function can be used to add delimiters and properly escape delimiters inside strings and recognizes both single ' and double " quotes as delimiters, as well as brackets [ and ].

Answer 6

I ran into a strange anomaly that would apply here. Using Google API and getting the reply in XML format, it was failing to convert to XML data type because of single quotes.

Replace(@Strip ,'''','')

was not working because the single quote was ascii character 146 instead of 39. So I used:

Replace(@Strip, char(146), '')

which also works for regular single quotes char(39) and any other special character.