Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Replace character

    Hi,

    DB2 9.1 z/os

    In the below two strings
    Code:
    1.  ABCD1EFGH&IJ{KL
      
    2.  MN2P@QRST[UV5

    I need a query to get a result for only alphabets and other than alphabets need to replace with space.

    The input string may be having Junk characters also

    Expected result

    Code:
    ABCD EFGH IJ KL
    
    MN P QRST UV


    Thanks,

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    I believe you could use XQUERY in 10 and I think 9.1 on z/os is EOS anyway so a move to a supported version would give you a heap of extra function
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  3. #3
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    In fact the XQUERY statement would be something like ..............
    update tab1 set c1 = xmlcast(xmlquery('fn:replace($C1,''[\~\@\%\^\*\(\)\{\}\?\[\]\`\<\>\,\.\:\;\+\=\_\-\$\#\!\&amp;0123456789]'', " ")') as varchar(20)) where xmlcast(xmlquery('fn:matches($C1,''[^a-zA-Z]'')') as integer) = 1

    I'm sure someone can tidy that up
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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