Subject Re: [firebird-support] Firebird 'like'
Author Ann W. Harrison
Jonathan Neve wrote:
> ... If I change the "AND C.Variete like '1AK'"
> clause to "AND C.Variete = '1AK'", it returns the correct rows.
> The records in the table don't seem to contain any trailing spaces, but
> the field type is a char(4),

Char fields are implicitly blank filled to full size. That doesn't
matter with equality comparisons because both strings are implicitly
blank filled to the length of the longer for equality tests in standard
SQL. LIKE follows different rules. If you want to use a LIKE with
a pattern that is shorter than the value being compared, either blank
pad the pattern to the correct length or terminate it with a %.

> I imagine if the field had been a varchar it would have worked...

That's right.
> So I suppose this is just Firebird working "as designed". :)

That's right too.

> But is
> there some simple way of getting the engine to trim the value for me so
> as to find the match, other than replacing "like" by "="? (I'd rather
> not use a UDF if I can help it.)

Don't use CHAR for varying length strings.