Re: CHAR LIKE VARCHAR

Ilja Golshtein wrote:
> Hi!
>
> I came across following difference
> between "LIKE" and "=" regarding
> CHARs and VARCHARs
>
> create table aa(f5 char(5), fv varchar(5));
> insert into aa values('str1', 'str1');
> select count(*) from aa where f5 = fv;
>
>>1
>
> select count(*) from aa where f5 like fv;
>
>>0
>
> I understand trailing spaces in CHAR are not
> significant though I expect DBMS shows
> consistent (and, ideally, clearly documented)
> behavior.

If you ask me (and it's too late to back out now :-) the whole behaviour
of CHAR(n) is wrong, broken and just a bad idea.

>>From my point of view in example above
> it would be nice to have the same result
> for both queries regardless it is 0 or 1.
>
> Of course, I may be wrong. Is there a clear
> concept behind the difference between "LIKE"
> and "="?

Well, you could argue that LIKE should ignore the trailing spaces (and
the only way to decide is to look at the SQL specs). The problem is,
that if f5=fv then presumably length(f5)=length(fv) and length(f5 ||
'+') = length(f5)+1

Of course, only the first of these is true because the whole idea of
char(n) is badly thought out. Either the value contains spaces or it
doesn't - unfortunately it's neither and both.
--
Richard Huxton
Archonet Ltd