Apostrophe/single quote at start of string
When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:
SELECT '''Hi There'
FROM dual;
would return
'Hi There
Apostrophe/single quote in the middle of a string
When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:
SELECT 'He''s always the first to arrive'
FROM dual;
would return
He's always the first to arrive
Apostrophe/single quote at the end of a string
When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:
SELECT 'Smiths'''
FROM dual;
would return
Smiths'
Apostrophe/single quote in a concatenated string
If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:
SELECT 'There' || '''' || 's Henry'
FROM dual;
would return
There's Henry
Reference: http://www.techonthenet.com/oracle/questions/quotes.php
