6 Replies Latest reply: Mar 8, 2013 9:52 AM by Murray Sobol RSS

    Replacing special characters

    Murray Sobol
      I have special characters in a varchar column that I need to remove.
      The characters are as follows:
      lv_single_quote := CHR(39);
      lv_double_quote := CHR(34);
      lv_ampersand := CHR(38);
      lv_tilde := CHR(126);
      lv_percent := CHR(37);
      lv_caret := CHR(94);

      The data bafore the update statement looks like this:
      QUANTITY_ENTRY_NBR LAST_PRODUCT_HAULED
      --------------------------------------------------------------------------------
      ------------------------------
      10889 smurf's

      I tried to replace the single_quote character with a space using this SQL:
      update s1_quantity_entry
      set last_product_hauled = REGEXP_REPLACE(last_product_hauled, 'CHR(39)', 'chr(40) ')
      where quantity_entry_nbr = 10889;

      It shows that one row was updated, but the data appears to be unchanged.

      Ideally, I would like the result to look like this:
      QUANTITY_ENTRY_NBR LAST_PRODUCT_HAULED
      --------------------------------------------------------------------------------
      ------------------------------
      10889 Smurfs

      Any assistance would be appreciated.
      Thanks
        • 1. Re: Replacing special characters
          Another_user
          update s1_quantity_entry
          set last_product_hauled = REPLACE(last_product_hauled, CHR(39), chr(40) )
          where quantity_entry_nbr = 10889;

          or just

          update s1_quantity_entry
          set last_product_hauled = REPLACE(last_product_hauled, CHR(39) )
          where quantity_entry_nbr = 10889;

          Edited by: Another_user on Mar 5, 2013 9:34 AM
          • 2. Re: Replacing special characters
            €$ħ₪
            try this...
            update s1_quantity_entry
            set last_product_hauled = replace(last_product_hauled,'''',' ')
            • 3. Re: Replacing special characters
              stefan nebesnak
              Murray Sobol wrote:
              I have special characters in a varchar column that I need to remove.
              The characters are as follows ...
              <tt>
              CHR(39)     <FONT
              style="BACKGROUND-COLOR: #D0F5A9">'</FONT>
              CHR(34)     <FONT
              style="BACKGROUND-COLOR: #D0F5A9">"</FONT >
              CHR(38)     <FONT
              style="BACKGROUND-COLOR: #D0F5A9">&</FONT >
              CHR(126) <FONT
              style="BACKGROUND-COLOR: #D0F5A9">~</FONT >
              CHR(37)     <FONT
              style="BACKGROUND-COLOR: #D0F5A9">%</FONT >
              CHR(94)     <FONT
              style="BACKGROUND-COLOR: #D0F5A9">^</FONT >
              </tt>
              {quote:title=Murray Sobol wrote:}{quote}
              I tried to replace the single_quote character with *a space* ...
              You can try this:
              <tt>
              select REGEXP_REPLACE(last_product_hauled,'[<FONT
              style="BACKGROUND-COLOR: #D0F5A9">"&~%</font>'<FONT
              style="BACKGROUND-COLOR: #D0F5A9">'^</FONT >]','<FONT
              style="BACKGROUND-COLOR: #FFFF33"> </FONT>') replaced_last_product_hauled from s1_quantity_entry;
              </tt>
              • 4. Re: Replacing special characters
                AlbertoFaenza
                Hi,

                TRANSLATE will work too and should perform better than regular expression:
                WITH mydata(txt) AS
                (SELECT ' This is a string with special characters:[" & ~ % '' ^ ]. Remove them' FROM DUAL
                ) 
                SELECT TRANSLATE(txt,'x"&~%''^','x') txt FROM mydata;
                
                TXT                                                            
                ---------------------------------------------------------------
                 This is a string with special characters:[      ]. Remove them
                Regards.
                Al
                • 5. Re: Replacing special characters
                  Ramu Pabbati
                  Hi,

                  Try using regex_replace in below way to replace all the special characters except alphabets and numbers with space.

                  *regexp_replace('String with special characters','[^[:alnum:]-]',' ')*

                  Regards,
                  Ramu Pabbati
                  • 6. Re: Replacing special characters
                    Murray Sobol
                    My question has been answered.