Re: Oracle7 SQLPLUS: question about CHAR and VARCHAR

In article <3fmgc2$qn4_at_unix1.sncc.lsu.edu>, hswang_at_unix1.sncc.lsu.edu (WANG) says:
>

 <snip>
>
>It looks like 'select Firstname||','||Lastname from T2;' works pretty good
>with manually inserted record, but doesn't work for those records transfered
>from the old table T1 whose column structure is defined as fixed length of char
>
>My question is why various length fields don't work for those records transfered
>from fixed length fields. I have already created several tables with huge number
>of records inputed and didn't realize they won't work for the above select
>statement until I saw the function of VARCHAR(). So I tried to transfer those
>records from the old table to my newly created table in order to avoid
>re-entering all the records again. It doesn't work except manually inserted
>record. So I want to know if there is any function I can use to modify field
>attribute of a existing table. If I can't do that, how can I transfer the record
>from the old tables to new tables with defined various length of char fields?
>

When you define a field as CHAR(), Oracle automatically pads the data in the field with spaces out to the length of the field. When you insert a row from a CHAR-defined field into a VARCHAR-defined field, the spaces come along for the ride, since they are part of the CHAR-defined field. When you manually insert a row into the VARCHAR field, spaces are not automatically added, so you don't see them.

What you want to do is to use the RTRIM() function when you insert a row from the CHAR field to the VARCHAR field. RTRIM() is defined in the SQL manual, and is used to remove extraneous spaces from the right side of the data. If the spaces are on the left, use LTRIM(). It'll be a little messy, but it's doable.

HTH
Mike Received on Fri Jan 20 1995 - 20:31:39 CET