Archives of the TeradataForum

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

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

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.

Regards,

Victor

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