How to handle a single quote in Oracle SQL

Question

How do I insert a record in a column having varchar data type having single quote in it?

Example: first name is ROBERT and last name is D'COSTA

Vincent Malgrat · Accepted Answer · 2016-02-25 07:56:07Z

155

Use two single-quotes

SQL> SELECT 'D''COSTA' name FROM DUAL;

NAME
-------
D'COSTA

Alternatively, use the new (10g+) quoting method:

SQL> SELECT q'$D'COSTA$' NAME FROM DUAL;

NAME
-------
D'COSTA

edited Feb 25, 2016 at 7:56

answered May 20, 2010 at 15:32

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

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

4 Comments

Richard-Degenne Over a year ago

What if we need two successive single quotes? Is this case the "alternative" method is meant for?

Vincent Malgrat Over a year ago

@RichouHunter You would use four single quotes : 'D''''COSTA' => D''COSTA. Alternatively: q'$D''COSTA$'

user2858650 Over a year ago

Thanks, this helped me! For reference, here's another Oracle document regarding the alternate quoting syntax for text literals that reads a little better than the linked PL/SQL doc.

Vincent Malgrat Over a year ago

@Dank I agree, the SQL reference is clearer than the PL/SQL doc on this subject (I've updated the link to the most recent version).

Mur3ph · Accepted Answer · 2017-03-21 14:22:36Z

2

I found the above answer giving an error with Oracle SQL, you also must use square brackets, below;

SQL> SELECT Q'[Paddy O'Reilly]' FROM DUAL;

Result: Paddy O'Reilly

answered Mar 21, 2017 at 14:22

Mur3ph

372 bronze badges

Collectives™ on Stack Overflow

How to handle a single quote in Oracle SQL

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related