9 Replies Latest reply: Nov 26, 2012 3:02 AM by ranit B RSS

    Removing Junk Characters.

    San Reddy
      Dear Friends,

      In our application, User copying some data from a document and pasting in a field "Comments".

      If that data consists anything like bullets,arrows of word document. It is inserting some junk characters into database like below.

      •     Analysys
      •     Do
      •     Now
      •     When
      •     As
      •     We

      don’t know how much he love sthe testing’I am not crazyh’


      I AM ‘USER’ 

      
      ï‚®
           Uu
           Yy
           tt


      Now user asking to remove all those Junk characters from Comments Column. Please help!

      Edited by: San Reddy on May 31, 2010 6:09 PM

      Edited by: San Reddy on May 31, 2010 6:18 PM
        • 1. Re: Removing Junk Characters.
          Saubhik
          Something like this ?
          SQL> WITH Sample_Data AS (SELECT 'â?¢ Analysys' str FROM DUAL UNION ALL
            2  SELECT 'donâ??t know how much he love sthe testingâ??I am not crazyhâ??' str FROM DUAL UNION ALL
            3  SELECT 'I AM â??USERâ?? ï?¨' str FROM DUAL 
            4  )
            5  SELECT REGEXP_REPLACE(str,'[^[a-z,A-Z,0-9]]*',' ') from Sample_Data
            6  ;
          
          REGEXP_REPLACE(STR,'[^[A-Z,A-Z,0-9]]*','')
          ----------------------------------------------------------------------------------------------------
              Analysys
          don   t know how much he love sthe testing   I am not crazyh
          I AM    USER
          • 2. Re: Removing Junk Characters.
            San Reddy
            Hi Saubhik,
            Thanx for your Helpful Suggestion, But with your function, it is deleting Spaces(' ') also. Iam getting output like this.


            SELECT COMMENTS FROM PB_INTERACTIONS
            -------------------------------------------------------------------

            •     Analysys
            •     Do
            •     Now
            •     When
            •     As
            •     We

            But always he doent think in the developer perspective,I don’t know how much he love sthe testing’I am not crazyh’


            I AM ‘USER’ 

            
            ï‚®
                 Uu
                 Yy
                 tt



            SELECT REGEXP_REPLACE(comments,'[^[a-z,A-Z,0-9]]*','')
            ----------------------------------------------------------------------------------------------------------
            AnalysysDoNowWhenAsWeButalwayshedoentthinkinthedeveloperperspective,IdontknowhowmuchhelovesthetestingIamnotcrazyhIAMUSERUuYytt


            In above i want remove only junk characters only. I dont want to remove extra spaces. Pls Help


            Thanks,
            Santhosh

            Edited by: San Reddy on May 31, 2010 6:44 PM

            Edited by: San Reddy on May 31, 2010 6:46 PM
            • 3. Re: Removing Junk Characters.
              Saubhik
              Try using REGEXP_REPLACE(str,'[^[a-z,A-Z,0-9,[:space:]]]*','') You can look at the Regular Expression and include/exclude the character as per your wish.
              • 4. Re: Removing Junk Characters.
                San Reddy
                Its working

                Thanx Saubhik
                • 5. Re: Removing Junk Characters.
                  San Reddy
                  The above solution removing Keyboard characters also. But i want to remove only non keyboard characters only.
                  • 6. Re: Removing Junk Characters.
                    San Reddy
                    Hi Saubhik,

                    Above query is removing Keyboard characters like (&,!,#,$,^..etc). But i want to remove only non keyboard characters like ••••••

                    Pls Help on this.


                    Thanx,
                    Santhosh
                    • 7. Re: Removing Junk Characters.
                      778106
                      You can use:

                      REGEXP_REPLACE(FIELD,'[^[!-~]]*',' ')

                      By this you can remove non keyboard characeters
                      • 8. Re: Removing Junk Characters.
                        user11344029
                        I am able to remove characters and spaces also, I want to keep spaces.

                        Thanks
                        • 9. Re: Removing Junk Characters.
                          ranit B
                          Hope this helps,...
                          select REGEXP_REPLACE ('Heãllço', '[^ -~]', '') from dual;
                          gives
                          Hello
                          This removes any special character i.e. any char outside the range of a Space(Ascii = 32) to Tilde(Ascii = 126).

                          Please check if this meets your requirement completely and let me know.