Header Ads

  • Recent Post

    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

    No comments

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad