Replace single quotes in SQL Server

Solution 1:

You need to double up your single quotes as follows:

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

Solution 2:

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

SQL uses two quotes to represent one in a string.

Solution 3:

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 Server 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 use 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.

Solution 4:

You could use char(39)

insert into my_table values('hi, my name'+char(39)+'s tim.')

Or in this case:

Replace(@strip,char(39),'')