how to remove whitespaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to remove whitespaces

  1. #1
    Join Date
    Jan 2002
    Posts
    83

    how to remove whitespaces

    hi

    i have a text file dump from ibm db2, where fields are enclosed in double quotes and a comma. But there are white spaces in the fileds after and before the data .

    How do i remove these through sqlloader.
    I am loading the data through sql loader

    The data is goin in fine but the whitespaces are also getting inserted.

    ICEMAN

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Use vi or notepad to replace the spaces...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Thumbs up

    If the white spaces number is consistent, you can load the data with the white spaces adn then use LTRIM & RTRIM to remove them, eg.

    SQL> create table test (test varchar2(30));

    Table created.

    SQL> insert into test values (' TEST1 ');

    1 row created.

    SQL> insert into test values (' TEST2 ');

    1 row created.

    SQL> commit;

    Commit complete.


    SQL> select * from test;

    TEST
    ------------------------------
    TEST1
    TEST2

    SQL> update test set test = RTRIM( LTRIM(test, ' '), ' ');

    2 rows updated.

    SQL> select * from test;

    TEST
    ------------------------------
    TEST1
    TEST2
    Otherwise, use INSTR() and SUBSTR() functions, to find the white space numbers in each row. You have to write a PL/SQL cursor to process each row, too.


    Hope that helps,

    clio_usa

    OCP 8/8i/9i DBA

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by adewri
    Use vi or notepad to replace the spaces...
    Hmmm. I thought this was an Oracle forum. You're fired.

    In SQL*Loader you can apply a SQL Function during the load process to do the RTRIM/LTRIM/TRIM there - more efficient than loading it then trimming. See http://download-west.oracle.com/docs...06.htm#1008153

    SQL*Loader will also do whitespace timming for you in some crcumstances .. see http://download-west.oracle.com/docs...06.htm#1007774
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by slimdave
    Hmmm. I thought this was an Oracle forum. You're fired.[/url]
    I need a job slimdave fired me... :(
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175
    Originally posted by slimdave
    Hmmm. I thought this was an Oracle forum. You're fired.

    In SQL*Loader you can apply a SQL Function during the load process to do the RTRIM/LTRIM/TRIM there - more efficient than loading it then trimming. See http://download-west.oracle.com/docs...06.htm#1008153

    SQL*Loader will also do whitespace timming for you in some crcumstances .. see http://download-west.oracle.com/docs...06.htm#1007774

    Only if the datatype of the fields is CHAR(), not a VARCHAR2(). Which is not so likely.


  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The problem with this methodology in your case is not the data type, but the fact that the data is delimited and quoted -- otherwise you could specify CHAR in the control file reference and data loaded to an underlying varchar2 filed would get trimmed.

    In your case you will have to apply a SQL operator to the field, as in the other doc link.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width