How Can I Insert a Single Quote into an Oracle VARCHAR Column?

Insert single quote in Oracle VARCHAR column

Special handling is required to avoid syntax errors when inserting records into VARCHAR columns containing single quotes. Here’s how to do it:

Method 1: Use double single quotes

Use two consecutive single quotes to escape embedded single quotes. For example, to insert the name "ROBERT D'COSTA":

<code class="language-sql">INSERT INTO table_name (first_name, last_name) VALUES ('ROBERT', 'D''COSTA');</code>

Method 2: Use the new reference method (Oracle 10g and higher)

Alternatively, you can use the new reference methods introduced in Oracle 10g. Enclose the string in single quotes, preceded by the letters "q$". For example:

<code class="language-sql">INSERT INTO table_name (first_name, last_name) VALUES ('ROBERT', q'$D'COSTA$'');</code>

Using either of these two methods, Oracle will correctly interpret the single quote as part of the data and not as a delimiter, ensuring that the insert operation succeeds.

The above is the detailed content of How Can I Insert a Single Quote into an Oracle VARCHAR Column?. For more information, please follow other related articles on the PHP Chinese website!