Remove pading from Table.

[EM1107]

Good morning everyone!

I was tasked on migrating tables from a SQL SERVER database to Oracle 9i. The task was completed succesfuly. This is what I assumed when I verify the ammount of data in each individual tables, until I notice that one of my table as padding in it. This stop the application from working properly. I have than try the following script to remvove the spaces place at the end of the entry:

UPDATE TABLE_NAME
SET COLNAME = RTRIM(COLNAME, ' ');
COMMIT;

Guess what it did not work. Is there an other way I can remove that padding from the columns!!!

Thanks in advance for your help!!

 

[Turkbear]

Hi,
What is the datatype of the 'padded' fields?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[KenCunningham]

What OS? Do you mean the column names have the unwanted padding or the data (or both)? If the latter it might be easier to write the data to a csv file and then edit it before reloading? Just an idea.

 

[EM1107]

Sorry about that I should have mentioned it!
All the column with padding are CHAR and VARCHAR2.

 

[hoinz]

Perhaps not just blanks, but other non printable characters, e.g. CR (aka hex 0x0d) ?

 

[EM1107]

An other thing the table name is fine. The data is the problem. I have also droped the table and recteate it. Than I have exported the data to a dilimited ASCII file, verify that padding was not from the SQL SERVER table and than import the data in the table using SQL Loader. I still have the same problem. The table still have the padding!!!

 

[EM1107]

An other thing I have tryed it via CSV, and before importing the data I have drop and recreated the table.
Still the same!!

 

[Turkbear]

Hi,
The Varchar2 columns should not need trimming..
( unless you are linking to CHAR ones)

For the char ,change them to Varchar2 then use Trim..

Alter table <tablename>
modify <charcolname> VARCHAR2(<samelength>)

Update <tablename> set <colname> = Trim(<colname>)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[hoinz]

You might want to check, if my guess above is correct:
select dump(column,16) from table_name;

 

[EM1107]

The Oracle version I work with is 9i. The table name is fine there is no padding in it. The data is the problem.

As mentioned before! I have drop the table, recreated it again and export the data from the SQL Server 2000 to a CSV file. I have verify if there was any padding and import the data in the table. There was no changes. The padding is still there.

Why? and how can I remove that padding as the script above did not work for me??

 

[SantaMufasa]

EM,

Did you do what Turkbear suggested? A CHAR column will ALWAYS contain trailing blank spaces as long as the column is NOT NULL and the data is not as long as the column maximum allows. If you do not want trailing blank spaces, then you must use VARCHAR2 (or VARCHAR, which translates to VARCHAR2).

If all of your text columns are VARCHAR2 and you have done an RTRIM(<column>)-function UPDATE to the previously padded columns, then you should be fine.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EM1107]

Thanks everyone!

This as work without any problems!!

I will modify my tables to use VARCHAR2 instead of CHAR!