Archives of the TeradataForum
Message Posted: Fri, 04 Aug 2006 @ 20:13:27 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|