Re: Substring Search

--=_alternative 0063B2A785256FD2_=
Content-Type: text/plain; charset="US-ASCII"

Armando wrote on 03/28/2005 01:06:39 PM:

> Hi Everyone,
>
> I've got 2 columns in a table that I need to be able to do a partial
> string search against. One column is a model number, the other a model
> name, and I'm having a bit of trouble determining how exactly to do
> this. (MyISAM tables on MySQL 4.0.17)
>
> For instance say there are a bunch of records that have a model number
> of 123456789. I need the user to be able to find all those records based

> on ANY partially matching search criteria (ie; 456). In the same query,
> I need to be able to look at the model name field and check for a
> partial match on the same criteria. The nearest I figure is I need to
> use the instr function in some way. Can someone lend me a hand? Cheers!
>
> Armando
>

Check out the LIKE, and REGEXP(RLIKE) comparators:

http://dev.mysql.com/doc/mysql/en/string-comparison-function s.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 0063B2A785256FD2_=--

Substring Search

Hi Everyone,

I've got 2 columns in a table that I need to be able to do a partial
string search against. One column is a model number, the other a model
name, and I'm having a bit of trouble determining how exactly to do
this. (MyISAM tables on MySQL 4.0.17)

For instance say there are a bunch of records that have a model number
of 123456789. I need the user to be able to find all those records based
on ANY partially matching search criteria (ie; 456). In the same query,
I need to be able to look at the model name field and check for a
partial match on the same criteria. The nearest I figure is I need to
use the instr function in some way. Can someone lend me a hand? Cheers!

Armando

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Substring Search

Thanks Shawn... upon reviewing the basics of the LIKE function (which I
was originally trying to use but it wasn't working) I found that my
problem was that I had entered my WHERE clause syntax incorrectly.
*pause to kick self in butt*

Another question too, do you know of any limitations of this function? I
couldn't really find any from a quick search but suspect if you're
dealing with large fields in the seacrh criteria it would be more of a
performance issue than anything else. Cheers!

Armando

SGreen@unimin.com wrote:
>
> Armando wrote on 03/28/2005 01:06:39 PM:
>
> > Hi Everyone,
> >
> > I've got 2 columns in a table that I need to be able to do a partial
> > string search against. One column is a model number, the other a model
> > name, and I'm having a bit of trouble determining how exactly to do
> > this. (MyISAM tables on MySQL 4.0.17)
> >
> > For instance say there are a bunch of records that have a model number
> > of 123456789. I need the user to be able to find all those records based
> > on ANY partially matching search criteria (ie; 456). In the same query,
> > I need to be able to look at the model name field and check for a
> > partial match on the same criteria. The nearest I figure is I need to
> > use the instr function in some way. Can someone lend me a hand? Cheers!
> >
> > Armando
> >
>
> Check out the LIKE, and REGEXP(RLIKE) comparators:
>
> http://dev.mysql.com/doc/mysql/en/string-comparison-function s.html
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Substring Search

--=_alternative 006BDD0585256FD2_=
Content-Type: text/plain; charset="US-ASCII"

You are correct, some substring searches can bring some servers to their
knees. How quick a substring query will be depends on the size of your
strings, how many strings you need to search, how many results you want,
and the substring pattern you are trying to match (if you are just
checking for a substring at the beginning of a field, that search could
use an index and could be very quick. All others would require scanning
each field's string for the pattern you seek). I don't see any other way
around using a substring search to find the records you want out of your
data.

Any limitations? I can't think of any off the top of my head but that
doesn't mean they do not exist. Maybe others on the list have run into
some type of limit??

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Armando wrote on 03/28/2005 02:09:50 PM:

> Thanks Shawn... upon reviewing the basics of the LIKE function (which I
> was originally trying to use but it wasn't working) I found that my
> problem was that I had entered my WHERE clause syntax incorrectly.
> *pause to kick self in butt*
>
> Another question too, do you know of any limitations of this function? I

> couldn't really find any from a quick search but suspect if you're
> dealing with large fields in the seacrh criteria it would be more of a
> performance issue than anything else. Cheers!
>
> Armando
>
> SGreen@unimin.com wrote:
> >
> > Armando wrote on 03/28/2005 01:06:39 PM:
> >
> > > Hi Everyone,
> > >
> > > I've got 2 columns in a table that I need to be able to do a
partial
> > > string search against. One column is a model number, the other a
model
> > > name, and I'm having a bit of trouble determining how exactly to do
> > > this. (MyISAM tables on MySQL 4.0.17)
> > >
> > > For instance say there are a bunch of records that have a model
number
> > > of 123456789. I need the user to be able to find all those records
based
> > > on ANY partially matching search criteria (ie; 456). In the same
query,
> > > I need to be able to look at the model name field and check for a
> > > partial match on the same criteria. The nearest I figure is I need
to
> > > use the instr function in some way. Can someone lend me a hand?
Cheers!
> > >
> > > Armando
> > >
> >
> > Check out the LIKE, and REGEXP(RLIKE) comparators:
> >
> > http://dev.mysql.com/doc/mysql/en/string-comparison-function s.html
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 006BDD0585256FD2_=--

Re: Substring Search

If you are looking for '456' in your sample then write query
SELECT * FROM table WHERE model_number LIKE '%456% OR model_name LIKE
'%456%';

The '%' is wildcard character for LIKE ;-)


Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)


----- Original Message -----
From: "Armando"
To:
Sent: Monday, March 28, 2005 8:06 PM
Subject: Substring Search


> Hi Everyone,
>
> I've got 2 columns in a table that I need to be able to do a partial
> string search against. One column is a model number, the other a model
> name, and I'm having a bit of trouble determining how exactly to do
> this. (MyISAM tables on MySQL 4.0.17)
>
> For instance say there are a bunch of records that have a model number
> of 123456789. I need the user to be able to find all those records based
> on ANY partially matching search criteria (ie; 456). In the same query,
> I need to be able to look at the model name field and check for a
> partial match on the same criteria. The nearest I figure is I need to
> use the instr function in some way. Can someone lend me a hand? Cheers!
>
> Armando
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=petr@practisoft.cz
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Substring Search

Fortunately the table that it's querying will only ever be about 2500
records mx. and the fields it's checking are varchars that are at most
about 30-40 characters in any given field so I don't think this will be
too bad for performance since the server is internal and relatively low
volume overall. Thanks again!

Armando

SGreen@unimin.com wrote:
> You are correct, some substring searches can bring some servers to their
> knees. How quick a substring query will be depends on the size of your
> strings, how many strings you need to search, how many results you want,
> and the substring pattern you are trying to match (if you are just
> checking for a substring at the beginning of a field, that search could
> use an index and could be very quick. All others would require scanning
> each field's string for the pattern you seek). I don't see any other way
> around using a substring search to find the records you want out of your
> data.
>
> Any limitations? I can't think of any off the top of my head but that
> doesn't mean they do not exist. Maybe others on the list have run into
> some type of limit??
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> Armando wrote on 03/28/2005 02:09:50 PM:
>
>
>>Thanks Shawn... upon reviewing the basics of the LIKE function (which I
>>was originally trying to use but it wasn't working) I found that my
>>problem was that I had entered my WHERE clause syntax incorrectly.
>>*pause to kick self in butt*
>>
>>Another question too, do you know of any limitations of this function? I
>
>
>>couldn't really find any from a quick search but suspect if you're
>>dealing with large fields in the seacrh criteria it would be more of a
>>performance issue than anything else. Cheers!
>>
>>Armando
>>
>>SGreen@unimin.com wrote:
>>
>>>Armando wrote on 03/28/2005 01:06:39 PM:
>>>
>>> > Hi Everyone,
>>> >
>>> > I've got 2 columns in a table that I need to be able to do a
>
> partial
>
>>> > string search against. One column is a model number, the other a
>
> model
>
>>> > name, and I'm having a bit of trouble determining how exactly to do
>>> > this. (MyISAM tables on MySQL 4.0.17)
>>> >
>>> > For instance say there are a bunch of records that have a model
>
> number
>
>>> > of 123456789. I need the user to be able to find all those records
>
> based
>
>>> > on ANY partially matching search criteria (ie; 456). In the same
>
> query,
>
>>> > I need to be able to look at the model name field and check for a
>>> > partial match on the same criteria. The nearest I figure is I need
>
> to
>
>>> > use the instr function in some way. Can someone lend me a hand?
>
> Cheers!
>
>>> >
>>> > Armando
>>> >
>>>
>>>Check out the LIKE, and REGEXP(RLIKE) comparators:
>>>
>>>http://dev.mysql.com/doc/mysql/en/string-comparison-funct ions.html
>>>
>>>Shawn Green
>>>Database Administrator
>>>Unimin Corporation - Spruce Pine
>>>
>>
>>--
>>MySQL Windows Mailing List
>>For list archives: http://lists.mysql.com/win32
>>To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org