7 Replies Latest reply: Nov 9, 2010 9:24 AM by Yasu RSS

    Removing non-printable characters

    Yasu
      Hi All,

      I was suppose to remove all non-printable characters, hence created below function. But in trouble for some rows in table.
      function ar1(i_value in varchar2)
      return varchar2
      as
             pattern varchar2(1000) := '][';
             l_strVal varchar2(1000);
      begin
             for i in 32 .. 126 loop
                pattern := pattern || case when chr(i) = '''' then ''''''
                                           when chr(i) in ('[', ']') then null
                                           else chr(i)
                                      end;
             end loop;
                l_strVal := regexp_replace(i_value, '([' || pattern || '])|.', '\1', 1, 0, 'n');
      return l_strVal;
      end;
      /
      Could anyone please help me, is this right way to do it.

      Problem is occurring for one row as shown below.
      SQL> select num, ar1(author) author1, author from doc where doc_num =37;
      
         NUM
      ----------
      AUTHOR1
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      AUTHOR
      --------------------------------------------------------------
        15098137
      OM LESRAVI{|~
      OM LES
      RAVI
      Its removing non-printable characters but not sure how these {|~ are included in the resultant.

      Would be great if anyone can help me.

      Edited by: YasserRACDBA on Nov 9, 2010 3:53 PM
        • 1. Re: Removing non-printable characters
          635471
          Seems like a regular expression would be a better solution. You can strip control codes with
          regexp_replace(my_string,'[[:cntrl:]]')
          and you could easily add more characters to the list the remove.
          • 2. Re: Removing non-printable characters
            Yasu
            Thanks....but even your method is giving worng result as shown below.
            SQL> select regexp_replace(author,'[[:cntrl:]]')
              2  from doc where doc_num =15098137;
            
            REGEXP_REPLACE(AUTHOR,'[[:CNTRL:]]')
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            OM LESRAVI{|~
            Is there any clue please...how come those {|~ are there??                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
            • 3. Re: Removing non-printable characters
              635471
              YasserRACDBA wrote:
              Is there any clue please...how come those {|~ are there??
              As I said, " ... and you could easily add more characters to the list ..."

              Those are not non-printable control codes so you add them to the list that you want to replace.
              • 4. Re: Removing non-printable characters
                Yasu
                I got your idea, but here there are no {|~  charcters in the string.

                My question is how come they are listed after the replace?
                • 5. Re: Removing non-printable characters
                  635471
                  What do you get for:
                  select author, dump(author)
                  from doc where doc_num =15098137;
                  • 6. Re: Removing non-printable characters
                    Yasu
                    SQL> select author,dump(author) from doc where doc_num =15098137;
                    
                    AUTHOR
                    --------------------------------------------------------------
                    DUMP(AUTHOR)
                    ------------------------------------------------------------------------------------------------------------------------------------------------------
                    Actually i was implementing above mentioned function from below thread, solution mentioned by Volder. But its not working...

                    Remove special characters from string using regexp_replace

                    Edited by: YasserRACDBA on Nov 9, 2010 5:42 PM

                    Edited by: YasserRACDBA on Nov 9, 2010 8:54 PM
                    • 7. Re: Removing non-printable characters
                      Yasu
                      Its working fine accordingly....I am really sorry David.

                      I would have checked myself before arguing with you.

                      Thanks a lot.

                      -Yasser