Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 04 Aug 2006 @ 20:13:27 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Missbehaving char/varchar data in teradata sql assistent
From:   Victor Sokovin

This is just how the CHAR(N) works.

Suppose you have a column with data type CHAR(10) and you want to store the value of 'abc' in it. What happens in the database is as follows: 'abs' is right padded with blanks to the full (and fixed!) length of the column, i.e., in this example you will find 'abc' and 7 blanks stored in the database.

If you store 'abc ' (with 1 blank), you will get 'abc ' and 6 blanks = 'abc' and 7 blanks. And so on.

So, the database does not remember how many blanks you stored and treats strings as 'abc', 'abc ', 'abc ' etc in the CHAR(10) column as equal. Equality of fixed length strings is defined modulo blanks (right padded).

I believe this is the generic definition of CHAR(N).

Some SQL tools might add or trim blanks if they are instructed so and you might have observed a difference between two tools. Probably not a bad situation as it draws attention to this tricky data type.

I think CHAR(N) is the all time champion in terms of the number of data quality problems it has generated.



  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020