Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: how to replace quotes in db2 sql

    the replace function cannot be used to replace single or double quotes. cna you let me know if there is any way to replace quotes with other character. thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Find out the ascii hex value, and then replace it with another hex value or space. For example X'22' is a double-quote.
    Ascii Table - ASCII character codes and html, octal, hex and decimal chart conversion
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2009
    Posts
    150
    you have to use double quotes not a single...

    Kara

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can replace single or double quotes just fine. The only thing to remember is to use proper escaping. Escaping in SQL is to double the escaped character, i.e. a single quote inside a string is ''.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    can i use the replace function in db2 to do that

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If this was a question, the answer is: yes, you can do that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2009
    Posts
    3
    what about using regular expression in replace or translate function like can i use something like ^[A-Za-z0-9] if i want to replace anything other than alpha numeric.

    Thanks

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    TRANSLATE and REPLACE don't work with regular expressions. But you can roll your own function if you need to: Bringing the Power of Regular Expression Matching to SQL
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Google found this old thread but the answer is not here. So here you go:
    Code:
    db2 "values replace(varchar('hoi''s'),x'27',space(1))"
    The single quote will be replaced by a space
    "hoi's" := "hoi s"

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z View Post
    Google found this old thread but the answer is not here. So here you go:
    Code:
    db2 "values replace(varchar('hoi''s'),x'27',space(1))"
    The single quote will be replaced by a space
    "hoi's" := "hoi s"
    How about this as I suggested over two years ago:
    Code:
    db2 "values replace(varchar('hoi''s'),x'27',x'20')"
    
    1
    -----
    hoi s
    
      1 record(s) selected.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or if someone doesn't like to use ASCII code points:
    Code:
    replace(varchar('hoi''s'), '''', ' ')
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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