Subject Re: [ib-support] Re: SELECT ... WHERE ... LIKE ... question (Firebird 1.02)
Author Nando Dessena
as Lucas has correctly explained:

>> So asking for a LIKE '__AB' will never get you any result
>> back since ALL fields in the char column have a fix
>> length of five and your asking for 4 chars.

The SQL standard, which Firebird follows as much as it's reasonable,
dictates that every comparison between char fields/constants of
different length is executed *after* padding the shorter operand with
spaces so it becomes the same length of the longer one.
That's what Firebird does, AFAIK, and that's why

wwyc> SELECT Code FROM <Table> WHERE Code = "01AB"

wwyc> does return this code; in this case SELECT does not have a problem
wwyc> with the 4 or 5 characters (I have tested this to make sure this is
wwyc> correct :-)).

Unfortunately the padding rule does not seem to be applied in case of
LIKE. I'm not knowledgeable enough about the SQL standard to say
whether this is a bug or not, but at least all you are observing has a
decent explaination.

My advice, FWIW, is to only use char columns when all the values are
of the same length, varchar in all the other cases. I see you have 4
character and 5 caharacter values, which would strike me as a source
of headaches if combined with the char datatype, even if I didn't knew
anything about how char padding is done. :-)

Nando mailto:nandod@...