Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered: escape aphostrophes in select

    Hi. Is any method to escape aphostrophes in value of string field.
    I know that i have to add another aphostrophe before, but it is not what i want. I mean:
    exmple: INSERT INTO table(column) VALUES ('It's mine it's mary's it's his'); -- it is wrong
    INSERT INTO table(column) VALUES ('It''s mine it''s mary''s it''s his'); -- it is ok
    but i want something like this:
    INSERT INTO table(column) VALUES ('<escape all '>It's mine</escape all '>');
    -one command to escape all aphostrophes
    Greetings
    Idzik

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    There is no SQL syntax to do this.
    Depending on the context where you need this SQL, you may of course write a script (perl, bash, sh, etc.) which converts
    '<escape all '>It's mine it's mary's it's his</escape all '>'
    into
    'It''s mine it''s mary''s it''s his'
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Sep 2006
    Posts
    3
    I can't use any script language.
    Querry I'm making is generated by xslt.
    And there is some string column, and another one is XML column, where i insert whole source xml document.
    I'm making it by <xsl:copy-of> so i can't make any replace.
    Greetings
    Idzik

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    while inserting into table use someother character than '

    insert into sample values ('rahul~s vikky~s ');
    select * from sample
    DETAIL
    ---------------------
    rahul~s vikky~s


    update sample set detail=replace(detail,'~','''');

    select * from sample
    DETAIL
    ---------------------
    rahul's vikky's

    --Rahul Singh

  5. #5
    Join Date
    Apr 2008
    Posts
    6
    can anyone please tell me how to escape the ' character. I need to use an account number in my query and the query will only fetch the number if it is enclosed in 'number' i.e. single inverted comma

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Use two consecutive apostrophes instead of the one you want.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2008
    Posts
    6
    Thanks. I have already tired that but it is not working. Any other ideas...Thank you.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    using two consecutive apostrophes does work

    maybe you're doing it wrong

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2008
    Posts
    6

    Talking

    I am working in Oracle Forms and Reports. It is not working there. May be giving double apostrophes works only on other platforms.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Sugam Khetrapal
    I have already tried that but it is not working. Any other ideas...
    It depends on from what you want the quote to be escaped.
    Doubling it is the way to escape it from SQL.
    Preceding it by a backslash is the way to escape it from a UNIX shell.
    Putting it inside double quotes could be the way to escape it from other environments.
    Maybe you have to combine two or more of these, depending on through what layers of interfacing your quoted text has to be passed.
    E.g., to escape a quote from both the shell and SQL, replace it by \'\'

    So, just tell us in which environment you are trying to enter that quote, and in what form it should arrive where, and we will tell you how to do so
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Apr 2008
    Posts
    6

    Talking

    Oracle Forms and Reports is the environment.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's interesting... because you posted in the DB2 forum

    i shall move this thread for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2008
    Posts
    6
    Thank You.

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    that's interesting... because you posted in the DB2 forum.
    i shall move this thread for you
    Actually, the original thread (dating from October 2006) never belonged in the Oracle forum...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Sugam Khetrapal
    I am working in Oracle Forms and Reports. It is not working there. May be giving double apostrophes works only on other platforms.
    That's possible, but unlikely: doubling the apostrophe is really the standard SQL way of escaping the apostrophe.
    Are you sure the double apostrophe is directly visible by SQL, and is not seen by (and substituted/interpreted by) any other environment (compiler, script, shell ...) ?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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