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

score 137 · Accepted Answer · answered Sep 17 '09 at 19:06

137

You need to double up your single quotes as follows:

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

answered Sep 17 '09 at 19:06

David Andres

31,351
7
46
36

I am facing the same problem but using your technique i get **`syntax error, unexpected '''' (T_CONSTANT_ENCAPSED_STRING)`** – always-a-learner Jul 06 '17 at 08:27
2

REPLACE(@WAREHOUSE, '''', '''''') – ladieu Jul 09 '21 at 18:26

score 31 · Answer 2 · edited Jul 28 '15 at 08:31

31

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

SQL uses two quotes to represent one in a string.

edited Jul 28 '15 at 08:31

Kees C. Bakker

32,294
27
115
203

answered Sep 17 '09 at 19:05

ScottLenart

1,160
1
12
15

score 26 · Answer 3 · edited Aug 09 '16 at 08:02

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.

Thanks for the tip, was looking for how to replace ' with '' :) — Daniel, Nov 13 '19 at 10:40

NicoJuicy · Answer 4 · 2021-02-22T02:38:32.177

10

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

edited Feb 22 '21 at 02:38

answered Jan 13 '17 at 08:16

NicoJuicy

3,435
4
40
66

1

The beauty of this solution is that it's easier to read in certain circumstances. Thank you. – SQLServerSteve Dec 02 '19 at 23:11
1

This is always my preferred way of doing it. Double and quadruple quotes looks messy and is harder to maintain. Thanks for the reminder. – Taylor Brown Apr 17 '20 at 18:08

score 4 · Answer 5 · answered Sep 17 '09 at 19:52

4

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

answered Sep 17 '09 at 19:52

Remus Rusanu

288,378
40
442
569

QUOTENAME is for T-SQL identifiers. Unfortunately not appropriate for data. – Suncat2000 Feb 22 '18 at 14:29

score 3 · Answer 6 · answered Sep 17 '09 at 19:06

3

Try escaping the single quote with a single quote:

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

answered Sep 17 '09 at 19:06

Yannick Motton

34,761
4
39
55

score 3 · Answer 7 · answered May 19 '22 at 08:04

3

We have to double the number of quotes.

To replace single quote :

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

To replace double quotes :

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

answered May 19 '22 at 08:04

Chamila Maddumage

3,304
2
32
43

score 1 · Answer 8 · answered Sep 05 '13 at 23:29

1

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;

answered Sep 05 '13 at 23:29

Brad303

1,161
8
10

It seems like the OP is not understanding why putting a single quote inside single quotes does not work. Changing the way identifiers are quoted is unlikely to fix that. Also, this question is 3 years old... – Jon Gjengset Sep 05 '13 at 23:50
1

The OP said his function's purpose was to replace a single quote. My solution is indeed a valid approach to that function. Also, If other users find that the answers to this question don't help with REPLACE(), as in my case, this solution may indeed be helpful. In short, my answer may be helpful to others, if not necessarily the OP. And what difference does the age of a question make if it's helpful to the community? – Brad303 Sep 07 '13 at 01:10

score 1 · Answer 9 · answered Apr 04 '17 at 19:39

1

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.

answered Apr 04 '17 at 19:39

Gus Hixson

11
1

1

There is no ASCII character 146. Extended ASCII defines other characters, like some international characters, graphics, and symbols. `char(146)` corresponds to a Latin Æ in that character set. If that expression works, it doesn't correspond to anything that makes sense. – Suncat2000 Feb 22 '18 at 14:56

user1474588 · Answer 10 · 2019-10-04T09:52:16.850

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.

edited Oct 04 '19 at 09:52

answered Oct 04 '19 at 06:57

user1474588

11
2

score 0 · Answer 11 · edited Aug 09 '16 at 08:04

0

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!

edited Aug 09 '16 at 08:04

Peter Mortensen

30,738
21
105
131

answered Jul 20 '12 at 05:31

jovenb

120
2
9

score 0 · Answer 12 · answered Sep 17 '09 at 19:05

0

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

answered Sep 17 '09 at 19:05

Esteban Küber

36,388
15
79
97

It can be done on the inserts aswell, so the admin is not only on the dba – Adriaan Stander Sep 17 '09 at 19:20
this implies sprocs open to sql injection are ok. i do not agree with this implication. – John Dhom Apr 09 '12 at 17:55
What if the coder interacting with the server instance is trying to deliberately create an SQL injection attack? There should be some validation/sanitation done on the T-SQL/database end as well, especially for stored procs as implied by @JohnDhom – mpag Jan 02 '18 at 21:12

score 0 · Answer 13 · edited Aug 09 '16 at 08:00

0

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

edited Aug 09 '16 at 08:00

Peter Mortensen

30,738
21
105
131

answered Sep 17 '09 at 19:13

AviD

12,944
7
61
91

score 0 · Answer 14 · answered Aug 14 '18 at 09:38

0

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

answered Aug 14 '18 at 09:38

Rohan

41
2

Replace single quotes in SQL Server

14 Answers14

Linked

Related