-
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
-
Use vi or notepad to replace the spaces...
Amar
"There is a difference between knowing the path and walking the path."
-
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
-
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
-
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."
-
Only if the datatype of the fields is CHAR(), not a VARCHAR2(). Which is not so likely.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|