Re: Re: Syntax for wildcard selection

On Wed, Aug 15, 2001 at 05:11:28PM -0700, Scott Holmes wrote:
> It would seem that my database has unseen garbage in the field being queried.
> On further testing I find that
>
> select *
> from people
> where peopcode LIKE 'AB%AH%'
> order by peopcode;
>
> works, however
>
> select *
> from people
> where peopcode LIKE 'AB%AH'
> order by peopcode;
>
> does not.  I do have nine records that meet the above criteria and are found
> if the pattern ends with '%' but not without it.

here's my guess --

you probably used

    create table people (
        ...
        peopcode CHAR(...)
        ...
    );

instead of

        peopcode VARCHAR(...)

the "char" type pads with blanks (ascii 32 in latin1 encoding)
to fill the size of the field. "varchar" truncates the data
only if the data exceeds the field size.

so
    like "%pat"
will match fields ENDING with "pat" -- for varchar that's
usually at the end of the data; for char, that's at the last few
characters as speficied by the length of the field.

thus, for a field containing
    "stuff like this           "
a LIKE "%this" would not match, but a LIKE "%this%" would, and so
would LIKE "%this           ".

--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
    - P.J.Lee ('79-'80)

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!