varchar data type space issue

Database

varchar data type space issue

Hi, we are migrating data from oracle to teradata,  got a issue here.There is  a varchar column, in oracle this column has 2 same values one with space and other without space.But when we migrate the data to teradata it is trimming spaces and showing the 2 values as same. Is there anyway we can store the data with spaces ? .

In Oracle if we do

sel * from table where col1='8692'

result:8692

sel * from table where col1='8692 '

result:8692

In Teradata

sel * from table where col1='8692'

result:8692

8692

But it should return only one row.

Can some one plese help me with the issue? Thanks

5 REPLIES
Supporter

Re: varchar data type space issue

this thread might explain how Teradata is dealing with varchars

http://www.teradataforum.com/teradata/20060804_222628.htm

Beside this I would consider it as a quite ugly implementation to consider chars with blanks at the end as different values to the non blanks values. Technically it might work but in reports etc. nobody will see the differences.

Supporter

Re: varchar data type space issue

P.S. only chance I see is to add one char at the end of each value (and extend the varchar def by one), e.g. concate a '|' during the load but in this case you would see it all the time.

Alternatiive add a char_length field to the table where you store the original char length.

Something like

 Create table varchar_tmp (id integer, kpi_dim varchar(11), dim_length smallint, kpi_value decimal(15,2)) primary index (id);

insert into varchar_tmp (1,'abc',3,12.2);
insert into varchar_tmp (2,'abc ',4,12.2);
insert into varchar_tmp (3,'abc ',6,12.2);

select * from varchar_tmp where kpi_dim = 'abc ' and dim_length=character('abc ');
select * from varchar_tmp where kpi_dim = 'abc' and dim_length=character('abc');
select * from varchar_tmp where kpi_dim = 'abc ' and dim_length=character('abc ');

drop table varchar_tmp;

But this would require also that this field is used in any type of grouping...

Overall remains not nice...

Senior Apprentice

Re: varchar data type space issue

Of course it's quite stupid to expect different meanings of 'abc' and 'abc ', nobody will see that difference, but leading blanks would be even worse :-)

And you can't even blame Oracle for this because Standard SQL allows both ways to compare strings (there's a PAD/NO PAD definition for character sets), most DBMSes follow the PAD rule.

But there is a simple solution:

select * from varchar_tmp where kpi_dim like 'abc ';

LIKE comparison always follows the NO PAD rule, the shorter string is never padded with blanks.

Dieter

Supporter

Re: varchar data type space issue

nice one :-)

But it will not deal with group by - in the meaning that they they will not aggregated to one row -, will it?

Senior Apprentice

Re: varchar data type space issue

Teradata will aggregate all in a single row (cause comparison is based on equality) and you'll get any of those values randomly.

Similar to aggregating a "not casespecific" column.

Dieter