Dealing with Apostrophes and Single Quotes in Strings SQL Formula

Question: How to handle apostrophes and single quotes in strings?
Answer: It is important to remember that in Oracle, the user needs to enclose strings in single quotes. The first quote denotes the beginning of the string and the second quote denotes the termination of the string.

If the user needs to deal with apostrophes/single quotes in strings, the solution depends on where the quote is located in the string.
See the following scenarios below.


Apostrophe/Single Quote at Start of String
When the apostrophe/single quote is at the start of the string, the solution is 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 at Middle of String
When the apostrophe/single quote is in the middle of the string, the solution is 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 End of String
When the apostrophe/single quote is at the end of a string, the solution is to enter 3 single quotes for Oracle to display a quote symbol. For example:
SELECT 'Smiths'''
FROM dual;
would return
Smiths'

Apostrophe/Single Quote at Concatenated of String
If the user needs to concatenate an apostrophe/single quote in a string, the solution is to 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

For more information, please visit http://www.techonthenet.com/oracle/questions/ (this is an external site)