Handling single quotes in Oracle - SQL Query

Since a string is passed in Oracle SQL via a single quote how to pass a value which already has a single quote?

How to identify field names which have a single quote in them?

The answer is simple to use multiple(four) single quotes and pass the required string. However time and again, I fail to recollect this simple thing. So, started scribbling...


This query gives data wherever a FIELDNAME has a single quote anywhere in the string:
SELECT * FROM TABLENAME WHERE FIELDNAME like '%'||''''||'%';


This query gives data where a FIELDNAME has a single quote as the last character in the string:
SELECT * FROM TABLENAME WHERE FIELDNAME like '%'||'''';


This query gives data where a FIELDNAME has a single quote as the first character in the string:
SELECT * FROM TABLENAME WHERE FIELDNAME like ''''||'%';


To replace a single quote with nothing use the below expression:
replace(a1.FIELDNAME , '''', '')

To insert or update a field value with single quote try in similar pattern:
To the first of a string:
UPDATE TABLENAME SET FIELDNAME = ''''||'2010' ;
This will update the field value as: '2010

To the end of a string:
UPDATE TABLENAME SET last_name = 'Jan'||'''' ;
This will update the field value as: Jan'

To the middle of a string:
UPDATE TABLENAME SET middle_name = 'Jan'||''''||'2010' ;
This will update the field value as: Jan'2010


The below link explains the above in a different and elaborative fashion:
http://www.techonthenet.com/oracle/questions/quotes.php