Re: [PHP-DB] Distinct Partial Matches: RegExp

2006-09-03 Thread Bastien Koert

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

2006-08-30 Thread Micah Stevens


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

2006-08-30 Thread Kevin Murphy
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

2006-08-30 Thread Micah Stevens


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

2006-08-30 Thread J R

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

2006-08-30 Thread Chris
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

2006-08-30 Thread Micah Stevens


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

2006-08-30 Thread J R

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

2006-08-30 Thread Chris

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