List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 26 1999 9:52pm
Subject:Inverse of ... like "...%"
View as plain text  
>>>>> "Fred" == Fred T Krogh <fkrogh@stripped> writes:

Fred> Given a primary key which is CHAR(8) BINARY, I would like to select all
Fred> entries which are a leading substring of a given k character string.
Fred> (Selecting all entries which start with a given substring can be done
Fred> efficiently with like, and '%', but what I want is apparently a bit on
Fred> the weird side.)

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

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

Fred> Any comments?

Fred> Thanks,
Fred>   Fred
Fred> fkrogh@stripped


Just a note:

The following works (but is not that fast):

SELECT * FROM table_name WHERE 'DBK' LIKE concat(column_name,"%");

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