How to Insert a Value that Contains an Apostrophe in PL/SQL?

PL/SQL, or Procedural Language/Structured Query Language, is an extension of SQL used for writing procedural code within Oracle databases. It is a proprietary procedural extension of SQL developed by Oracle Corporation specifically for Oracle Database. It combines the power of SQL with procedural constructs like loops, conditions, and exception handling. It is a blocked programming language, programming units can be named or unnamed blocks. Unnamed blocks are never stored in the database.

Understanding the Issue with Apostrophes

The most probable case with the need to use apostrophes is when dealing with string types. Before understanding how to deal with apostrophes let us understand what string type means and how is it related to apostrophes.

Strings are a datatype of PL/SQL. There are various types of string available in PL/SQL, a few of them are as follows:

• CHAR
• VARCHAR2
• RAW
• NCHAR
• NVARCHAR2
• ROWID
• UROWID

Let us create a sample table to see how to insert string values.

CREATE TABLE test (
    name VARCHAR2(15),
    id INT
);

Now let us insert string values into it. To do that we will surround the values in quotes.

INSERT ALL
        INTO test(name, id) VALUES ('Dhruv', 1) 
        INTO test(name, id) VALUES ('Aayush', 2)
SELECT * FROM DUAL;

SELECT * FROM test;

Output:

test table

Note: Don’t worry about DUAL, it is just syntax for inserting values. DUAL comes at places where you have nothing to select from. Like in MySQL, you can write “SELECT ‘abc’” but in PL/SQL you need to write “SELECT ‘abc’ FROM DUAL”. The INSERT ALL statement used above requires a SELECT statement and DUAL is used to fill the empty FROM.

Notice how the integers don’t need quotes around them while we need to surround the strings in quotes.

Now let us try to insert a value with an apostrophe in it.

INSERT INTO test VALUES ('Aaka'sh', 3);

The database throws an error

Output:

Error

Although the error does not directly mean it is related to the extra apostrophe in the string, it is caused by that. The database treats the value `’Aaka’sh’` as two separate words including one complete word `’Aaka’` and another value starting from `sh’` but not complete yet. Therefore, it asks for a missing comma to separate these values.

How to Insert Values with an Apostrophe

Now let us see how to insert values with an apostrophe in them.

Let us create a table that will have all the string types and insert values with apostrophes into it.

Note: RowID types are hexadecimal so they will never contain an apostrophe.

CREATE TABLE test_dq(
        char_column CHAR(20),
        varchar2_column VARCHAR2(20),
        raw_column RAW(20),
        nchar_column NCHAR(20),
        nvarchar2_column NVARCHAR2(20)
)

Method 1: Use Single Quotes Twice to Escape Single Quotes

When the string is enclosed in single quotes, single quotes have a special meaning to them. They are used to notify the starting and ending of the string. To escape the special meaning we can write the single quote twice consecutively. Let us see the following example.

INSERT INTO test_dq(char_column, varchar2_column, raw_column, nchar_column,    nvarchar2_column) 
  VALUES('''a''bc''', '''d''ef''', utl_raw.cast_to_raw('''g''hi'''), N'''j''kl''', N'''m''no''');
SELECT * FROM test_dq;

Output:

Use single quotes twice to escape single quotes

See how the method can be used to put quotes anywhere (front, middle & end) in the string.

Method 2: Use Literal Quoting

This method is specific to the Oracle database used for escaping single quotes. The syntax of the literal quoted string is as follows

q’<delimeter1>string<delimeter2>’

The delimiter can be any character except space, tab, and return. If the delimiter1 is one of [, {, <, or (, then the delimiter2 must be the corresponding ], }, >, or ). In all other cases, delimeter1 and delimeter2 should be the same.

Following are some examples of literal quoted strings.

q’[Armaan%]’
q’#Disc’’#’
q’<Hello World $$>’
q’”BBllc”’

These allow you to put as many single quotes inside the string without worrying. Let us see the example below.

TRUNCATE TABLE test_dq;
INSERT INTO test_dq(char_column, varchar2_column, raw_column, nchar_column,    nvarchar2_column) 
   VALUES(q'#'a'bc'#', q'['d'%f']', utl_raw.cast_to_raw(q'{'g&hi'}'), Nq'?'j@'kl'?', Nq'!'m*'no'!');
SELECT * FROM test_dq;

Output:

Using Literal Quoting

Here q’#’a’bc’#’ uses # as the delimiter, q'[‘d’%f’]’ uses [ and ] as the delimiters, q'{‘g&hi’}’ uses { and } as the delimiters, Nq’?’j@’kl’?’ uses ? as the delimiter and Nq’!’m*’no’!’ uses ! as the delimiter.

Method 3: Use CHR(39)

We can use the ASCII value of `’` (39) to insert it into strings. This can be done by concatenating different strings and putting CHR(39) in between them. In the following the concatenation is done using the `||` operator.

TRUNCATE TABLE test_dq;
INSERT INTO test_dq(char_column, varchar2_column, raw_column, nchar_column,    nvarchar2_column) 
    VALUES(CHR(39) || 'a' || CHR(39) || 'bc' || CHR(39), CHR(39) || 'd' || CHR(39) || 'ef' || CHR(39), utl_raw.cast_to_raw(CHR(39) || 'g' || CHR(39) || 'hi' || CHR(39)), NCHR(39) || N'j' || NCHR(39) || N'kl' || NCHR(39), NCHR(39) || N'm' || NCHR(39) || N'no' || NCHR(39));
SELECT * FROM test_dq;

Output:

Output

Conclusion

Overall we have seen that we can enclose strings in quotes in Oracle DB. However, to put apostrophes into strings themselves we need to escape them or use some other mechanism. There are three ways of doing this, the first being to write the single quotes twice consecutively. The second is to use string literals and the third is to use the CHR function. Overall it is not very difficult to incorporate quotes in strings and can be done using any of the above-mentioned methods.