81

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

ngrashia
  • 9,869
  • 5
  • 43
  • 58
subhashis
  • 4,629
  • 8
  • 37
  • 52

2 Answers2

154

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
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • What if we need two successive single quotes? Is this case the "alternative" method is meant for? – Richard-Degenne Dec 21 '15 at 11:32
  • @RichouHunter You would use four single quotes : `'D''''COSTA' => D''COSTA`. Alternatively: `q'$D''COSTA$'` – Vincent Malgrat Dec 21 '15 at 12:30
  • Thanks, this helped me! For reference, here's another [Oracle document](https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617) regarding the alternate quoting syntax for text literals that reads a little better than the linked PL/SQL doc. –  Feb 24 '16 at 20:20
  • @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). – Vincent Malgrat Feb 25 '16 at 07:57
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

Mur3ph
  • 37
  • 2