Extra space is getting added with data while loading data into varchar data type column

Database
Enthusiast

Extra space is getting added with data while loading data into varchar data type column

Hi All,

 I am getting  extra space while inserting integer data into varchar data type column.

Please explain the reason behind this.

create volatile table rowhash_my,no log

(

col1l varchar(20)

)

on commit preserve rows;

insert into rowhash_my values (123);

 

select char_length(trim(col1l)) ,trim(col1l),col1l from rowhash_my

Thanks,

sai

2 REPLIES
Enthusiast

Re: Extra space is getting added with data while loading data into varchar data type column

Please respond on this

Senior Supporter

Re: Extra space is getting added with data while loading data into varchar data type column

you rely on implicit conversions (you insert a int to a varchar) and that is never recommended as you don't know which rules are followed. 

I guess you see the placeholder for the possible sign...

Try to do an explicit conversion while doing the insert.

create volatile table rowhash_my,no log
(
col1l varchar(20)
)
on commit preserve rows;
insert into rowhash_my values (123);
insert into rowhash_my values (-123);
 
insert into rowhash_my values (cast(456 as varchar(20)));
insert into rowhash_my values (cast(-456 as varchar(20)));

select char_length(trim(col1l)) ,char_length(col1l) ,trim(col1l),col1l from rowhash_my