Replace single quote in sql server

Question

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

But when I insert 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

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

Answer 2

Try Replace(@strip,'''',''))

SQL uses two quotes to represent one in a string.

Answer 3

Try escaping the single quote with a single quote:

Replace(@strip, '''', '')

Answer 4

You need to double up your single quotes as follows:

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

Answer 5

Besides needing to escape the quote (by using double quotes), you've laso confused the names of variables: you're using @var and @strip, instead of @CleanString and @strStrip...

Answer 6

If you really must completely strip out the single quotes you can do this:

Replace(@strip, '''', '')

However, ordinarily you'd replace ' with '' and this will make SQL happy when querying the database. The trick with any of the built-in SQL functions (like replace) is that they too require you to double up your single quotes.

So to replace ' with '' in code you'd do this:

Replace(@strip, '''', '''''')

Of course... in some situations you can avoid having to do this entirely if you utilize parameters when querying the database. Say you're querying the database from a .NET application, then you'd use the SqlParameter class to feed the SqlCommand parameters for the query and all of this single quote business will be taken care of automatically. This is usually the preferred method as SQL parameters will also help prevent SQL injection attacks.

Answer 7

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 ].