Simple example: CHAR to VARCHAR2
A table, with a possibly old fashioned or even wrong data type
char, is created …
create table tq84_char_2_varchar2 (
id number,
txt char(20)
);
… and populated with some data:
insert into tq84_char_2_varchar2 values ( 1, 'one');
insert into tq84_char_2_varchar2 values (42, 'forty-two');
insert into tq84_char_2_varchar2 values (99, 'ninte-nine');
commit;
Oracle right fills data in char columns with spaces, so each length of txt is 20:
select length(txt) from tq84_char_2_varchar2;
Change the data type:
alter table tq84_char_2_varchar2 modify txt varchar2(20);
The data type has changed, but the data is still right padded with spaces:
select length(txt) from tq84_char_2_varchar2;
Therefore, we remove the spaces on the right side of txt:
update tq84_char_2_varchar2 set txt = rtrim(txt);
select length(txt) from tq84_char_2_varchar2;
--
-- TXT LENGTH(TXT)
-- -------------------- -----------
-- one 3
-- forty-two 9
-- ninte-nine 10