Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Arrow Unanswered: Insert Single Quote for A Field Value

    Hello Everyone,

    I am trying to set value for a field , and I want to have a string single quoted.But query throws error,seems single quote is not allowed in a field.

    Can you please suggest how I should be able to do this.

    update table t1 set query='select field1,'xxxx' || field2 from dual;' where <condition>;

    Thanks,
    Vinay

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    From the manual:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#i42617

    Quote Originally Posted by Oracle manual
    To represent one single quotation mark within a literal, enter two single quotation marks.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I find the "q" - function quite convenient (you don't have to count quotation marks in complex situations):

    the syntax is : q'[<text_with_quotes>]'

    Code:
    SQL> select q'['This is the test's result']' result from dual;
    
    RESULT
    ---------------------------
    'This is the test's result'
    
    SQL>
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  4. #4
    Join Date
    Jul 2012
    Posts
    4

    Talking

    Quote Originally Posted by vinay4889 View Post
    Hello Everyone,

    I am trying to set value for a field , and I want to have a string single quoted.But query throws error,seems single quote is not allowed in a field.

    Can you please suggest how I should be able to do this.

    update table t1 set query='select field1,'xxxx' || field2 from dual;' where <condition>;

    Thanks,
    Vinay
    You have to use quote "". Ex: strSQL = "Select field, ..."

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ndphuong View Post
    You have to use quote "". Ex: strSQL = "Select field, ..."
    Completely wrong. Double quotes are used for object names. They are not used for string literals.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, wrong as far as Oracle is concerned. Seeing "strSQL", maybe it is valid in some other database (such as MS SQL Server; just a blind guess).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •