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

Answer 1

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

Answer 2

Use single quote in oracle 12 C.

SELECT 'Paddy O''Reilly' FROM DUAL;

Answer 3

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