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
[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
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
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
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
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
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
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
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!!!