Reputation: 1657

Replace single quotes in SQL Server

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

Upvotes: 68

Answers (14)

Chamila Maddumage

Reputation: 3876

We have to double the number of quotes.

To replace single quote :

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

To replace double quotes :

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

Upvotes: 4

NicoJuicy

Reputation: 3528

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

Upvotes: 12

user1474588

Reputation: 11

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.

Upvotes: 1

Rohan

Reputation: 41

select replace ( colname, '''', '') AS colname FROM .[dbo].[Db Name]

Upvotes: 0

Gus Hixson

Reputation: 11

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.

Upvotes: 1

jovenb

Reputation: 120

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!

Upvotes: 0

Steve Wortham

Reputation: 22250

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.

Upvotes: 28

AviD

Reputation: 13112

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

Upvotes: 0

ScottLenart

Reputation: 1210

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

SQL uses two quotes to represent one in a string.

Upvotes: 33

Brad303

Reputation: 1189

If escaping your single quote with another single quote isn't working for you (like it didn't for one of my recent REPLACE() queries), you can use SET QUOTED_IDENTIFIER OFF before your query, then SET QUOTED_IDENTIFIER ON after.

For example

SET QUOTED_IDENTIFIER OFF;

UPDATE TABLE SET NAME = REPLACE(NAME, "'S", "S");

SET QUOTED_IDENTIFIER OFF;

Upvotes: 1

Remus Rusanu

Reputation: 294417

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

Upvotes: 4