Subject Re: [ib-support] Re: SELECT ... WHERE ... LIKE ... question (Firebird 1.02)
Author Lucas Franzen

> I have a CHAR(5) field with codes like:
> 0101, 0102, 0201, 01AB, 01DAB, 02AB, 02AAB, 02BC, 0304, 0310
> and many alike.

your problem is that the field is defined as CHAR(5) and not as
The CHAR fields are padded with blanks, varchar fields aren't.

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.

You may change your column to VARCHAR, make sure you trim the values and
it will work.

> When selecting I need to get the __AB's with two characters in front
> of the letters, but not the ones with __AAB I would get when using
> a "%AB".

If you stay with CHAR(5) you can use:

LIKE '__AB%'

In this case you will get 01AB, but not 01DAB

so, maybe:

LIKE '__AB%' or LIKE '___AB' will do it, too.

> In the meantime I have found out that when using the wildcard _ in a
> LIKE query, this is a problem for both Interbase and Firebird: when I
> add a space after the "AB", so using WHERE Code LIKE "__AB ", then I
> do get the correct ones.
> Apparently the LIKE with _ wildcards looks at all 5 characters of the
> Code field. Can you confirm this?

See above.
It has nothing to do with LIKE, it's the CHAR definition.