From: Michael Widenius
Date: March 26 1999 9:52pm
Subject: Inverse of ... like "...%"
List-Archive: http://lists.mysql.com/mysql/1046
Message-Id: <14076.330.440596.466167@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "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

Hi!

Just a note:

The following works (but is not that fast):

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

Regards,
Monty