Archives of the TeradataForum

Message Posted: Wed, 10 Dec 2003 @ 14:26:00 GMT

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

Anomy Anom wrote:

> The % wildcard on the LIKE predicate causes non-standard behaviour.
>
> create table test1000
> (name char(10) );
>
> insert into test1000 values ('NEW YORK');
>
> /* The following correctly retrieves NEW YORK */
> select * from test1000 where name = 'NEW YORK';
>
> /* The following does not retrieve anything */
> select * from test1000 where name like '%YORK';
>
> /* When we append two spaces to the end, since name is defined as
> char(10), it retrieves NEW YORK correctly */
>
> select * from test1000 where name like '%YORK  ';
>
> Other databases don't exhibit this behaviour.  Is this a bug, or is this how it's supposed to work?

All three queries work as defined by SQL:1999.

A CHAR column is always padded with blanks to it's maximum length, so you'll have to use:

select * from test1000 where TRIM(TRAILING FROM name) like '%YORK';

Dieter

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