Archives of the TeradataForum

Message Posted: Thu, 11 Dec 2003 @ 08:46:26 GMT

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

String comparison with CHAR is perhaps less strange than it seems. By declaring a column CHAR(n) we accept that strings with less than n characters will be modified, i.e., right-padded with blanks. It is, therefore, quite natural to accept comparison modulo trailing spaces. What alternative could we have?

If in the OP's example

where CITY='NEW YORK'

the SQL engine returned no rows, users would have to enter the padded version with two spaces:

where CITY='NEW YORK '. For just 'York' they would have to enter six spaces etc. Not a really nice idea. It is more convenient if the SQL engine does this work for us. It takes the original string literal 'NEW YORK' and implicitly converts it to CHAR(10), i.e., pads two spaces, and then compares (the 'usual' way) this modified string with the values in the column. It is the same precision level as we have accepted with the CHAR data type from the beginning.

LIKE is meant to perform pattern search in strings and it is natural to let it have full access to all characters in the column, including the trailing spaces. It is pretty much the only operator of its kind and it is good to use its full power, when it is required. If it added trailing spaces in the same way as '=' does, then we would be restricted in the choice of patterns. It is better to have LIKE very precise and not allow it to do any implicit conversions because it does a more complex task than '='.

So, current rules seem to be OK if we want to use CHAR. With objects like geographical locations VARCHAR would probably be more natural but there could still be good reasons to go for CHAR. For example, to save storage space by compressing the column on the list of 'popular' locations. It is better to have LIKE very precise and not allow it to do any implicit conversions because it does a more complex task than '='.

Regards,

Victor

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