List:General Discussion« Previous MessageNext Message »
From:Fred T. Krogh Date:March 26 1999 4:55pm
Subject:Inverse of ... like "...%"
View as plain text  
Given a primary key which is CHAR(8) BINARY, I would like to select all
entries which are a leading substring of a given k character string.
(Selecting all entries which start with a given substring can be done
efficiently with like, and '%', but what I want is apparently a bit on
the weird side.)

Thus DKB matches DKB, DK, and D.  I'm guessing the most efficient way to
do this lookup is to simply select on each of these one at a time.
Using a function would require looking at every row, not just those
entries which start with D?  (The given string will typically be only 3
or 4 characters.)  Something like "DKB" like col_x% would be ideal for
this, but I don't think anything like this is available.  (For the
curious, I have a hierarchy describing areas of computational
mathematics in ever finer detail.  A person can request notification
when a new entry is made on a level they indicate or in any lower
level.  Thus a person wanting to track everything in DK and below wants
to know that something has just been added to DKB, for example.)

An alternative would be to select on col_x like "D%" and store in a
temporary table, select the "D"'s, then select on on col_x like "DK%"
from the temporary table to a new temporary table, etc.  This looks ugly
to me.

Any comments?

Thanks,
  Fred
fkrogh@stripped


Thread
Inverse of ... like "...%"Fred T. Krogh26 Mar
  • Inverse of ... like "...%"Michael Widenius26 Mar
  • Re: Inverse of ... like "...%"Fred Read26 Mar
Re: Inverse of ... like "...%"Fred T. Krogh27 Mar
Re: Inverse of ... like "...%"Fred Lindberg28 Mar