Re: [PHP-DB] Distinct Partial Matches: RegExp
SQL Server and DB2 (version dependant) don't support regexp in sql
Bastien
From: Micah Stevens <[EMAIL PROTECTED]>
To: J R <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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
Re: [PHP-DB] 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: [PHP-DB] Distinct Partial Matches: RegExp
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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!!!
Re: [PHP-DB] 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 <[EMAIL PROTECTED]> 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: [PHP-DB] 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: [PHP-DB] Distinct Partial Matches: RegExp
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 <[EMAIL PROTECTED]> 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!!!
Re: [PHP-DB] 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: [PHP-DB] Distinct Partial Matches: RegExp
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
Re: [PHP-DB] 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
[PHP-DB] Distinct Partial Matches: RegExp
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
