SQL Server: how to insert the ' character

Question

I need to insert some string with ' in it, for example 'x.y.x', After some research I discovered the following syntax:

table_b.element = 'replace('x.y.x','',''')'

the problem is that SQL Server gives me an error:

Unclosed quotation mark after the character string ')

How can I solve this case? I spent about 2 hours on this.

Is there an escape character that I need to use?

user330315 · Accepted Answer · 2011-05-14 14:09:51Z

3

You need to write two single quotes:

replace('x.y.x','','''')

(This is true for all SQL databases)

answered May 14, 2011 at 14:09

user330315

Sign up to request clarification or add additional context in comments.

Comments

Alex K. · Accepted Answer · 2011-05-14 14:19:58Z

0

' is escaped with '', so if its a literal string within an SQL statement;

update t set fld = '''x.y.x'''

If your passing in a value to a procedure for example or your constructing a statement then using your client language you must replace(data, "'", "''") (or use prepared statements)

answered May 14, 2011 at 14:19

Alex K.

177k32 gold badges276 silver badges299 bronze badges

2 Comments

Chen Kinnrot Over a year ago

'replace('x.y.z',"'","''")' gives me a Unclosed quotation mark after the character string ')'

Alex K. Over a year ago

Its unlear what you need? you would rarely need to use replace() within an sql statement to escape anything; if your passing/building a string containing ' then you need to escape it in your client language

Collectives™ on Stack Overflow

SQL Server: how to insert the ' character

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related