Handle single quotes in Oracle SQL

When inserting data containing single quote characters into a column with VARCHAR data type, it is crucial to properly handle single quotes to avoid syntax errors.

Solution

There are two main ways to deal with single quotes in Oracle SQL:

Use double single quotes

To insert single quotes into a VARCHAR column, use two consecutive single quotes. For example:

INSERT INTO table_name (column_name) VALUES ('D''COSTA');

This inserts the value "D'COSTA" into the specified column.

Use new reference method (Oracle 10g)

Oracle 10g and later allows you to use a new reference method:

INSERT INTO table_name (column_name) VALUES q'$D'COSTA$';

In this method, the value is enclosed in two single quotes, preceded by the letter "q".

Example:

The following query demonstrates the use of these methods:

SELECT 'D''COSTA' name FROM DUAL;
SELECT q'$D'COSTA$' NAME FROM DUAL;

Both queries will return the value of "D'COSTA".