Distinct Partial Matches: RegExp

--Apple-Mail-25-431796079
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

This might be really easy, but I'm just not sure how to write this
query and my searching on google isn't finding me things, probably
because I am searching for the wrong terms.

I have a bunch of records where the "area" column is like:

animal-dog-5
animal-dog-3
animal-cat-1
animal-cat-22
animal-bird-5

What I want to do is run a distinct query on just the part previous
to the number.

animal-dog
animal-cat
animal-bird

So in other words, something like this, but I am not sure if this is
the right way to go:

$query = "SELECT DISTINCT area FROM table WHERE REGEXP
'anynumberofletters dash anynumberofletters dash '"


Of course, I could be barking up the wrong tree with the REGEXP
thing. Anyone care to point me in the right direction?


--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326




--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326



--Apple-Mail-25-431796079--

Re: Distinct Partial Matches: RegExp

Select DISTINCT area from table like '$searchterms%';

In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
wildcards.. '_' is one character, '%' is any number of chars.

-Micah


Kevin Murphy wrote:
> This might be really easy, but I'm just not sure how to write this
> query and my searching on google isn't finding me things, probably
> because I am searching for the wrong terms.
>
> I have a bunch of records where the "area" column is like:
>
> animal-dog-5
> animal-dog-3
> animal-cat-1
> animal-cat-22
> animal-bird-5
>
> What I want to do is run a distinct query on just the part previous to
> the number.
>
> animal-dog
> animal-cat
> animal-bird
>
> So in other words, something like this, but I am not sure if this is
> the right way to go:
>
> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
> 'anynumberofletters dash anynumberofletters dash '"
>
>
> Of course, I could be barking up the wrong tree with the REGEXP thing.
> Anyone care to point me in the right direction?
>
>
> --Kevin Murphy
> Webmaster: Information and Marketing Services
> Western Nevada Community College
> www.wncc.edu
> 775-445-3326
>
>
>
>
> --Kevin Murphy
> Webmaster: Information and Marketing Services
> Western Nevada Community College
> www.wncc.edu
> 775-445-3326
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Distinct Partial Matches: RegExp

--Apple-Mail-29-434139844
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Well, its not really a search.... that would be way easier. :-) What
I'm looking for is a query that will give me the complete list of
items that are distinct, minus the last number after the last hyphen.

animal-dog
animal-cat
animal-bird

--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326


On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote:

>
> Select DISTINCT area from table like '$searchterms%';
>
> In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
> wildcards.. '_' is one character, '%' is any number of chars.
>
> -Micah
>
>
> Kevin Murphy wrote:
>> This might be really easy, but I'm just not sure how to write this
>> query and my searching on google isn't finding me things, probably
>> because I am searching for the wrong terms.
>>
>> I have a bunch of records where the "area" column is like:
>>
>> animal-dog-5
>> animal-dog-3
>> animal-cat-1
>> animal-cat-22
>> animal-bird-5
>>
>> What I want to do is run a distinct query on just the part
>> previous to the number.
>>
>> animal-dog
>> animal-cat
>> animal-bird
>>
>> So in other words, something like this, but I am not sure if this
>> is the right way to go:
>>
>> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
>> 'anynumberofletters dash anynumberofletters dash '"
>>
>>
>> Of course, I could be barking up the wrong tree with the REGEXP
>> thing. Anyone care to point me in the right direction?
>>
>>
>> --Kevin Murphy
>> Webmaster: Information and Marketing Services
>> Western Nevada Community College
>> www.wncc.edu
>> 775-445-3326
>>
>>
>>
>>
>> --Kevin Murphy
>> Webmaster: Information and Marketing Services
>> Western Nevada Community College
>> www.wncc.edu
>> 775-445-3326
>>
>>
>>
>


--Apple-Mail-29-434139844--

Re: Distinct Partial Matches: RegExp

Oh! I misunderstood..

This is tougher, you're selecting the entire set, so you won't have
anything after 'WHERE', but you're defining how the returned items are
formatted, this goes before table selection:

Select DISTINCT SUBSTRING_INDEX(`area`, '-', 2) from table;

I didn't test this, but it should work. Look up SUBSTRING_INDEX in the
docs..

-Micah

Kevin Murphy wrote:
> Well, its not really a search.... that would be way easier. :-) What
> I'm looking for is a query that will give me the complete list of
> items that are distinct, minus the last number after the last hyphen.
>
> animal-dog
> animal-cat
> animal-bird
>
> --Kevin Murphy
> Webmaster: Information and Marketing Services
> Western Nevada Community College
> www.wncc.edu
> 775-445-3326
>
>
> On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote:
>
>>
>> Select DISTINCT area from table like '$searchterms%';
>>
>> In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
>> wildcards.. '_' is one character, '%' is any number of chars.
>>
>> -Micah
>>
>>
>> Kevin Murphy wrote:
>>> This might be really easy, but I'm just not sure how to write this
>>> query and my searching on google isn't finding me things, probably
>>> because I am searching for the wrong terms.
>>>
>>> I have a bunch of records where the "area" column is like:
>>>
>>> animal-dog-5
>>> animal-dog-3
>>> animal-cat-1
>>> animal-cat-22
>>> animal-bird-5
>>>
>>> What I want to do is run a distinct query on just the part previous
>>> to the number.
>>>
>>> animal-dog
>>> animal-cat
>>> animal-bird
>>>
>>> So in other words, something like this, but I am not sure if this is
>>> the right way to go:
>>>
>>> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
>>> 'anynumberofletters dash anynumberofletters dash '"
>>>
>>>
>>> Of course, I could be barking up the wrong tree with the REGEXP
>>> thing. Anyone care to point me in the right direction?
>>>
>>>
>>> --Kevin Murphy
>>> Webmaster: Information and Marketing Services
>>> Western Nevada Community College
>>> www.wncc.edu
>>> 775-445-3326
>>>
>>>
>>>
>>>
>>> --Kevin Murphy
>>> Webmaster: Information and Marketing Services
>>> Western Nevada Community College
>>> www.wncc.edu
>>> 775-445-3326
>>>
>>>
>>>
>>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Distinct Partial Matches: RegExp

------=_Part_51215_22552427.1156986070033
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

i'm a bit confused. if i'm getting you right heres my 2 cents:

first you do sql query using DISTINCT

when the result are returned to you, you can then run thru the result array
using preg_replace. heres an example:

$aVar = array(
'animal-dog-5',
'animal-dog-3',
'animal-cat-1',
'animal-cat-22',
'animal-bird-5',
);

$aResult = array();
foreach ($aVar as $value) {
$tmp = preg_replace('/-\d*$/', '', $value);
$aResult[$tmp] = $tmp;
// if you want to conserve a bit of resources asign NULL;
// $aResult[$tmp] = NULL;
}
var_dump($aResult);


hth,

john

p.s.
i'm not sure if it is possible to use regular expression in a sql query. can
anyone comment on this? thanks.

On 8/31/06, Kevin Murphy wrote:
>
> Well, its not really a search.... that would be way easier. :-) What
> I'm looking for is a query that will give me the complete list of
> items that are distinct, minus the last number after the last hyphen.
>
> animal-dog
> animal-cat
> animal-bird
>
> --
> Kevin Murphy
> Webmaster: Information and Marketing Services
> Western Nevada Community College
> www.wncc.edu
> 775-445-3326
>
>
> On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote:
>
> >
> > Select DISTINCT area from table like '$searchterms%';
> >
> > In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
> > wildcards.. '_' is one character, '%' is any number of chars.
> >
> > -Micah
> >
> >
> > Kevin Murphy wrote:
> >> This might be really easy, but I'm just not sure how to write this
> >> query and my searching on google isn't finding me things, probably
> >> because I am searching for the wrong terms.
> >>
> >> I have a bunch of records where the "area" column is like:
> >>
> >> animal-dog-5
> >> animal-dog-3
> >> animal-cat-1
> >> animal-cat-22
> >> animal-bird-5
> >>
> >> What I want to do is run a distinct query on just the part
> >> previous to the number.
> >>
> >> animal-dog
> >> animal-cat
> >> animal-bird
> >>
> >> So in other words, something like this, but I am not sure if this
> >> is the right way to go:
> >>
> >> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
> >> 'anynumberofletters dash anynumberofletters dash '"
> >>
> >>
> >> Of course, I could be barking up the wrong tree with the REGEXP
> >> thing. Anyone care to point me in the right direction?
> >>
> >>
> >> --Kevin Murphy
> >> Webmaster: Information and Marketing Services
> >> Western Nevada Community College
> >> www.wncc.edu
> >> 775-445-3326
> >>
> >>
> >>
> >>
> >> --Kevin Murphy
> >> Webmaster: Information and Marketing Services
> >> Western Nevada Community College
> >> www.wncc.edu
> >> 775-445-3326
> >>
> >>
> >>
> >
>
>
>


--
GMail Rocks!!!

------=_Part_51215_22552427.1156986070033--

Re: Distinct Partial Matches: RegExp

> i'm not sure if it is possible to use regular expression in a sql query.
> can
> anyone comment on this? thanks.

Mysql & Postgres can. I'm sure others can too.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Distinct Partial Matches: RegExp

You can do regular expression matching in MySQL and I think a few other
servers too, but that's not the same as regular expression replacement
like you can do with PHP.. It just returns a boolean true/false
depending on whether or not the match works.

-Micah

J R wrote:
> i'm a bit confused. if i'm getting you right heres my 2 cents:
>
> first you do sql query using DISTINCT
>
> when the result are returned to you, you can then run thru the result
> array
> using preg_replace. heres an example:
>
> $aVar = array(
> 'animal-dog-5',
> 'animal-dog-3',
> 'animal-cat-1',
> 'animal-cat-22',
> 'animal-bird-5',
> );
>
> $aResult = array();
> foreach ($aVar as $value) {
> $tmp = preg_replace('/-\d*$/', '', $value);
> $aResult[$tmp] = $tmp;
> // if you want to conserve a bit of resources asign NULL;
> // $aResult[$tmp] = NULL;
> }
> var_dump($aResult);
>
>
> hth,
>
> john
>
> p.s.
> i'm not sure if it is possible to use regular expression in a sql
> query. can
> anyone comment on this? thanks.
>
> On 8/31/06, Kevin Murphy wrote:
>>
>> Well, its not really a search.... that would be way easier. :-) What
>> I'm looking for is a query that will give me the complete list of
>> items that are distinct, minus the last number after the last hyphen.
>>
>> animal-dog
>> animal-cat
>> animal-bird
>>
>> --
>> Kevin Murphy
>> Webmaster: Information and Marketing Services
>> Western Nevada Community College
>> www.wncc.edu
>> 775-445-3326
>>
>>
>> On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote:
>>
>> >
>> > Select DISTINCT area from table like '$searchterms%';
>> >
>> > In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
>> > wildcards.. '_' is one character, '%' is any number of chars.
>> >
>> > -Micah
>> >
>> >
>> > Kevin Murphy wrote:
>> >> This might be really easy, but I'm just not sure how to write this
>> >> query and my searching on google isn't finding me things, probably
>> >> because I am searching for the wrong terms.
>> >>
>> >> I have a bunch of records where the "area" column is like:
>> >>
>> >> animal-dog-5
>> >> animal-dog-3
>> >> animal-cat-1
>> >> animal-cat-22
>> >> animal-bird-5
>> >>
>> >> What I want to do is run a distinct query on just the part
>> >> previous to the number.
>> >>
>> >> animal-dog
>> >> animal-cat
>> >> animal-bird
>> >>
>> >> So in other words, something like this, but I am not sure if this
>> >> is the right way to go:
>> >>
>> >> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
>> >> 'anynumberofletters dash anynumberofletters dash '"
>> >>
>> >>
>> >> Of course, I could be barking up the wrong tree with the REGEXP
>> >> thing. Anyone care to point me in the right direction?
>> >>
>> >>
>> >> --Kevin Murphy
>> >> Webmaster: Information and Marketing Services
>> >> Western Nevada Community College
>> >> www.wncc.edu
>> >> 775-445-3326
>> >>
>> >>
>> >>
>> >>
>> >> --Kevin Murphy
>> >> Webmaster: Information and Marketing Services
>> >> Western Nevada Community College
>> >> www.wncc.edu
>> >> 775-445-3326
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Distinct Partial Matches: RegExp

------=_Part_52001_32135272.1156989094769
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

thanks for the info. didn't know this. :). i'll be googling how this is
done. :D and perhaps post back a solution to your problem kevin, if i find
one :).

On 8/31/06, Micah Stevens wrote:
>
>
> You can do regular expression matching in MySQL and I think a few other
> servers too, but that's not the same as regular expression replacement
> like you can do with PHP.. It just returns a boolean true/false
> depending on whether or not the match works.
>
> -Micah
>
> J R wrote:
> > i'm a bit confused. if i'm getting you right heres my 2 cents:
> >
> > first you do sql query using DISTINCT
> >
> > when the result are returned to you, you can then run thru the result
> > array
> > using preg_replace. heres an example:
> >
> > $aVar = array(
> > 'animal-dog-5',
> > 'animal-dog-3',
> > 'animal-cat-1',
> > 'animal-cat-22',
> > 'animal-bird-5',
> > );
> >
> > $aResult = array();
> > foreach ($aVar as $value) {
> > $tmp = preg_replace('/-\d*$/', '', $value);
> > $aResult[$tmp] = $tmp;
> > // if you want to conserve a bit of resources asign NULL;
> > // $aResult[$tmp] = NULL;
> > }
> > var_dump($aResult);
> >
> >
> > hth,
> >
> > john
> >
> > p.s.
> > i'm not sure if it is possible to use regular expression in a sql
> > query. can
> > anyone comment on this? thanks.
> >
> > On 8/31/06, Kevin Murphy wrote:
> >>
> >> Well, its not really a search.... that would be way easier. :-) What
> >> I'm looking for is a query that will give me the complete list of
> >> items that are distinct, minus the last number after the last hyphen.
> >>
> >> animal-dog
> >> animal-cat
> >> animal-bird
> >>
> >> --
> >> Kevin Murphy
> >> Webmaster: Information and Marketing Services
> >> Western Nevada Community College
> >> www.wncc.edu
> >> 775-445-3326
> >>
> >>
> >> On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote:
> >>
> >> >
> >> > Select DISTINCT area from table like '$searchterms%';
> >> >
> >> > In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
> >> > wildcards.. '_' is one character, '%' is any number of chars.
> >> >
> >> > -Micah
> >> >
> >> >
> >> > Kevin Murphy wrote:
> >> >> This might be really easy, but I'm just not sure how to write this
> >> >> query and my searching on google isn't finding me things, probably
> >> >> because I am searching for the wrong terms.
> >> >>
> >> >> I have a bunch of records where the "area" column is like:
> >> >>
> >> >> animal-dog-5
> >> >> animal-dog-3
> >> >> animal-cat-1
> >> >> animal-cat-22
> >> >> animal-bird-5
> >> >>
> >> >> What I want to do is run a distinct query on just the part
> >> >> previous to the number.
> >> >>
> >> >> animal-dog
> >> >> animal-cat
> >> >> animal-bird
> >> >>
> >> >> So in other words, something like this, but I am not sure if this
> >> >> is the right way to go:
> >> >>
> >> >> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
> >> >> 'anynumberofletters dash anynumberofletters dash '"
> >> >>
> >> >>
> >> >> Of course, I could be barking up the wrong tree with the REGEXP
> >> >> thing. Anyone care to point me in the right direction?
> >> >>
> >> >>
> >> >> --Kevin Murphy
> >> >> Webmaster: Information and Marketing Services
> >> >> Western Nevada Community College
> >> >> www.wncc.edu
> >> >> 775-445-3326
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> --Kevin Murphy
> >> >> Webmaster: Information and Marketing Services
> >> >> Western Nevada Community College
> >> >> www.wncc.edu
> >> >> 775-445-3326
> >> >>
> >> >>
> >> >>
> >> >
> >>
> >>
> >>
> >
> >
>
>


--
GMail Rocks!!!

------=_Part_52001_32135272.1156989094769--

Re: Distinct Partial Matches: RegExp

J R wrote:
> thanks for the info. didn't know this. :). i'll be googling how this is
> done. :D and perhaps post back a solution to your problem kevin, if i find
> one :).

No need to google, go straight to the source:

http://dev.mysql.com

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Distinct Partial Matches: RegExp

SQL Server and DB2 (version dependant) don't support regexp in sql

Bastien


>From: Micah Stevens
>To: J R
>CC: php-db@lists.php.net
>Subject: Re: [PHP-DB] Distinct Partial Matches: RegExp
>Date: Wed, 30 Aug 2006 18:29:19 -0700
>
>
>You can do regular expression matching in MySQL and I think a few other
>servers too, but that's not the same as regular expression replacement like
>you can do with PHP.. It just returns a boolean true/false depending on
>whether or not the match works.
>
>-Micah
>
>J R wrote:
>>i'm a bit confused. if i'm getting you right heres my 2 cents:
>>
>>first you do sql query using DISTINCT
>>
>>when the result are returned to you, you can then run thru the result
>>array
>>using preg_replace. heres an example:
>>
>>$aVar = array(
>> 'animal-dog-5',
>> 'animal-dog-3',
>> 'animal-cat-1',
>> 'animal-cat-22',
>> 'animal-bird-5',
>> );
>>
>>$aResult = array();
>>foreach ($aVar as $value) {
>> $tmp = preg_replace('/-\d*$/', '', $value);
>> $aResult[$tmp] = $tmp;
>> // if you want to conserve a bit of resources asign NULL;
>> // $aResult[$tmp] = NULL;
>>}
>>var_dump($aResult);
>>
>>
>>hth,
>>
>>john
>>
>>p.s.
>>i'm not sure if it is possible to use regular expression in a sql query.
>>can
>>anyone comment on this? thanks.
>>
>>On 8/31/06, Kevin Murphy wrote:
>>>
>>>Well, its not really a search.... that would be way easier. :-) What
>>>I'm looking for is a query that will give me the complete list of
>>>items that are distinct, minus the last number after the last hyphen.
>>>
>>>animal-dog
>>>animal-cat
>>>animal-bird
>>>
>>>--
>>>Kevin Murphy
>>>Webmaster: Information and Marketing Services
>>>Western Nevada Community College
>>>www.wncc.edu
>>>775-445-3326
>>>
>>>
>>>On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote:
>>>
>>> >
>>> > Select DISTINCT area from table like '$searchterms%';
>>> >
>>> > In SQL, you can use the 'LIKE' keyword along with the '%' and '_'
>>> > wildcards.. '_' is one character, '%' is any number of chars.
>>> >
>>> > -Micah
>>> >
>>> >
>>> > Kevin Murphy wrote:
>>> >> This might be really easy, but I'm just not sure how to write this
>>> >> query and my searching on google isn't finding me things, probably
>>> >> because I am searching for the wrong terms.
>>> >>
>>> >> I have a bunch of records where the "area" column is like:
>>> >>
>>> >> animal-dog-5
>>> >> animal-dog-3
>>> >> animal-cat-1
>>> >> animal-cat-22
>>> >> animal-bird-5
>>> >>
>>> >> What I want to do is run a distinct query on just the part
>>> >> previous to the number.
>>> >>
>>> >> animal-dog
>>> >> animal-cat
>>> >> animal-bird
>>> >>
>>> >> So in other words, something like this, but I am not sure if this
>>> >> is the right way to go:
>>> >>
>>> >> $query = "SELECT DISTINCT area FROM table WHERE REGEXP
>>> >> 'anynumberofletters dash anynumberofletters dash '"
>>> >>
>>> >>
>>> >> Of course, I could be barking up the wrong tree with the REGEXP
>>> >> thing. Anyone care to point me in the right direction?
>>> >>
>>> >>
>>> >> --Kevin Murphy
>>> >> Webmaster: Information and Marketing Services
>>> >> Western Nevada Community College
>>> >> www.wncc.edu
>>> >> 775-445-3326
>>> >>
>>> >>
>>> >>
>>> >>
>>> >> --Kevin Murphy
>>> >> Webmaster: Information and Marketing Services
>>> >> Western Nevada Community College
>>> >> www.wncc.edu
>>> >> 775-445-3326
>>> >>
>>> >>
>>> >>
>>> >
>>>
>>>
>>>
>>
>>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php