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:
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:
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:
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.
Contact Us