Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: How to search mysql?

Re: How to search mysql?

From: Bob Hairgrove <rhairgroveNoSpam_at_Pleasebigfoot.com>
Date: Fri, 03 May 2002 14:39:01 GMT
Message-ID: <3cd29fed.14440654@news.ch.kpnqwest.net>


On Fri, 03 May 2002 14:30:57 GMT, wjdandreta_at_worldnet.att.net (Bill Dandreta) wrote:

>We have a mysql parts database and it is indexed by manufacturers part
>number. We want to be able to search it either by using the 1st few
>characters of the number and return numbers that begin with these
>characters or by matching a sequence of characters within the number.
>
>Example 1:
>
>part number = ans234756
>
>search for ans23 and retrieve all part numbers that begin with ans23.
>
>Example 2:
>
>part number = ans234756
>
>seacrh for s234 and retrieve all part numbers that contain s234.
>
>Does anyone know how to do these searches quickly? Any help will be
>appreciated.
>

It would be better to split up the part number into separate fields for this kind of searching; most database systems cannot search text fields efficiently unless an index is present, but if you search on substrings, the index cannot be used, i.e. the engine does a full table scan, unless you include the beginning of the string ... then it can use the index.

Anyway, you need to use the "LIKE" keyword and one or more wildcards:

>search for ans23 and retrieve all part numbers that begin with ans23.
SELECT part_no FROM parts WHERE part_no LIKE 'ans23%';

>seacrh for s234 and retrieve all part numbers that contain s234.
SELECT part_no FROM parts WHERE part_no LIKE '%s234%';

If you know the position within the string, you can use '_' instead of '%':

>seacrh for ns2 and retrieve all part numbers that contain ns2 from the second character:
SELECT part_no FROM parts WHERE part_no LIKE '_ns2%';

Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Fri May 03 2002 - 09:39:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US