1 Reply Latest reply: Nov 19, 2012 4:50 PM by SomeoneElse RSS

    Oracle SQL trim() vs MS Excel clean()

    975098
      I have some data being extracted from Oracle 11 db views into CSV files.
      When importing the data into some other system, said other system's data importer crashes.
      I can open the CSV files in MS Excel, apply its clean() function to some columns, paste the values of that, and then the CSV file is a bit smaller, unprintable characters are removed, and data importer in new system is OK.
      Its a cumbersome process.
      I tried Oracle SQL trim() but it doesn't remove all the chars, it seems, that MS Excel clean() function removes.
      Is there a comparable Oracle SQL function that does what [MS Excel clean()|http://office.microsoft.com/en-us/excel-help/clean-HP005209014.aspx] does?

      Edited by: 972095 on Nov 19, 2012 2:35 PM
        • 1. Re: Oracle SQL trim() vs MS Excel clean()
          SomeoneElse
          I've used: regexp_replace(your_string,'[[:cntrl:]]') for this.
          SQL> declare
            2     s varchar2(30) := chr(10) || 'hello' || chr(10);
            3  begin
            4     dbms_output.put_line('*' || s || '*');
            5     dbms_output.put_line('*' || regexp_replace(s,'[[:cntrl:]]') || '*');
            6  end;
            7  /
          *
          hello
          *
          *hello*
          
          PL/SQL procedure successfully completed.