OraQA

Oracle Question and Answer


Latest Comments

  • Frank Zhou:
    Gmyers, Your simpler method doesn’t work, it didn’t put all cases into consideration. For...

  • gmyers:
    Or a simple select str_num, case when length(replace(str_num, ’)')) = length(replace(str_n...

  • stewstryker:
    Latest version is 3.1. Man, this thread is old!

  • prashant.pawar:
    I could able to resolve this issue. my files under $ORACLE_HOME/network/me sg/ were corrupted. copied...

Comments RSS feed


  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed
« How to turn rows into columns

How to encode a BLOB to base64? »

How to escape single quotes in strings

March 20th, 2006 By Eddie Awad

Just add one more single quote next to the one you want to escape. For example:

SQL> select 'eddie''s stuff'
  2  from dual
  3  /

'EDDIE''SSTUF
-------------
eddie's stuff

SQL> select 'the sopranos'''
  2  from dual
  3  /

'THESOPRANOS'
-------------
the sopranos'

SQL> select 'ain' || '''' || ' t that nice?'
  2  from dual
  3  /

'AIN'||''''||'TTH
-----------------
ain' t that nice?

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.  

2 Responses to “How to escape single quotes in strings”

  1. TMoore Says:
    March 21st, 2006 at 3:33 am

    You can also put:

    SELECT 'alan'||chr(39)||'s database' FROM Dual

    chr( ) take chance to write any character

  2. austrin Says:
    March 21st, 2006 at 11:15 am

    The alternative quoting mechanism introduced with 10g should be mentioned as well.
    e.g. (i hope this example gets formatted right):

    q'(name LIKE '%DBMS_%%')'
q''

    A link to the documentation:
    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref378

    BR,
    Martin

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question
« How to turn rows into columns

How to encode a BLOB to base64? »