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
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
No comments
Please refrain for marketing messages and unnecessary back links.