Or you could use like '__AB%'

-----Original Message-----
From: Lucas Franzen [mailto:luc@...]
Sent: 13 February 2003 14:22
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Re: SELECT ... WHERE ... LIKE ... question
(Firebird 1.02)



Jack,

> 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
VARCHAR(5).
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.



Luc.


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/