Re: REGEXP help Finding phone numbers (nnn) nnn-nnnn format SOLVED
This seems to do it:
SELECT phone_work FROM leads WHERE phone_work REGEXP '[(]{1}([0-9]){3}[)]{1}[
]?([^0-1]){1}([0-9]){2}[ ]?[-]?[ ]?([0-9]){4}'
- Original Message
From: Paul Nowosielski [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, December 3, 2008 2:39:54 PM
Subject: REGEXP help Finding phone numbers (nnn) nnn- format
Hi,
Please, can anyone lend a hand in helping pullout
phone numbers from the DB that only match
the format (nnn) nnn- ?
SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?'
I've been trying to lick this for hours now with
no avail.
Thank you,
Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP help Finding phone numbers (nnn) nnn-nnnn format
On Wednesday 03 December 2008 08:39:54 Paul Nowosielski wrote:
Hi,
Please, can anyone lend a hand in helping pullout
phone numbers from the DB that only match
the format (nnn) nnn- ?
([0-9]{3}) [0-9]{3}-[0-9]{4}
I think
HTH
W
SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?'
I've been trying to lick this for hours now with
no avail.
Thank you,
Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REGEXP help Finding phone numbers (nnn) nnn-nnnn format
Hi, Please, can anyone lend a hand in helping pullout phone numbers from the DB that only match the format (nnn) nnn- ? SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?' I've been trying to lick this for hours now with no avail. Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL REGEXP help
Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL REGEXP help
Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike
Re: MYSQL REGEXP help
Hi, [ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp] because, In your query, '!' is an Operator and ? is a wild character. Only wildcharacters should be follow the Operators. Try with. SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 1:36 PM Subject: MYSQL REGEXP help Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL REGEXP help
Hello, this doesn't work: mysql SELECT 'oer bv' REGEXP '[b|^b](!?[v$|v])'; ++ | 'oer bv' REGEXP '[b|^b](!?[v$|v])' | ++ | 1 | ++ 1 row in set (0.00 sec) He shouldn't select this one, because it says 'bv' and no other b Mike ViSolve DB Team schreef: Hi, [ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp] because, In your query, '!' is an Operator and ? is a wild character. Only wildcharacters should be follow the Operators. Try with. SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 1:36 PM Subject: MYSQL REGEXP help Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL REGEXP help
Hi, Try with mysql select 'oer bv' REGEXP '(^b|[[:blank:]])(!?v|$v)'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 5:40 PM Subject: Re: MYSQL REGEXP help Hello, this doesn't work: mysql SELECT 'oer bv' REGEXP '[b|^b](!?[v$|v])'; ++ | 'oer bv' REGEXP '[b|^b](!?[v$|v])' | ++ | 1 | ++ 1 row in set (0.00 sec) He shouldn't select this one, because it says 'bv' and no other b Mike ViSolve DB Team schreef: Hi, [ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp] because, In your query, '!' is an Operator and ? is a wild character. Only wildcharacters should be follow the Operators. Try with. SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 1:36 PM Subject: MYSQL REGEXP help Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RegExp Help
Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: Id Title Address City State Zip 1 Joe 1313 Mockingbird Lane TransylvaniaPA 02098 ^ To: Id Title Address City State Zip 1 Joe 1313 Mockingbird Lane TransylvaniaPA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
wow, one post, two mistakes. how right you are. sorry. update 02093_xdir_links SET title = REPLACE(\,,title); you may/may not need to escape the . hth Jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] ve.com cc: Subject: RE: RegExp Help 01/21/2004 01:47 PM Please respond to bcohen you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^ To: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regexp Help !!!
Ok, I have a list/database of words that follows as ... Top ... Top/Arts/Food Top/Arts/Food/Country ... Top/World/America Top/World/Japan Top/World/Japan/Economy Top/World/Japan/Food Top/World/Japan/Food/Country Top/World/Japan/Food/Country/By_Chef ... How can I setup a regexp query/filter such that I can choose the prefix and the number of / in the results? Example: How can I query/filter the list such that I am looking for all Top/World/ matches that have only one more word after them. So my only results from the example about would be Top/World/America and Top/World/Japan. Then I would do the same for Top/World/Japan and get Top/World/Japan/Economy and then Top/World/Japan/Food. This is part of the program so I can count the number of / in the query/filter string. I jsut want to exclude the extra matches. Any thoughts? Thanks Jon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Regexp Help !!!
-Original Message- From: Jon Shoberg [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 13, 2002 4:47 PM To: Beginners (E-mail) Cc: *MySQL mail list; Jon Shoberg Subject: Regexp Help !!! Ok, I have a list/database of words that follows as ... Top ... Top/Arts/Food Top/Arts/Food/Country ... Top/World/America Top/World/Japan Top/World/Japan/Economy Top/World/Japan/Food Top/World/Japan/Food/Country Top/World/Japan/Food/Country/By_Chef ... How can I setup a regexp query/filter such that I can choose the prefix and the number of / in the results? Example: How can I query/filter the list such that I am looking for all Top/World/ matches that have only one more word after them. So my only results from the example about would be Top/World/America and Top/World/Japan. Then I would do the same for Top/World/Japan and get Top/World/Japan/Economy and then Top/World/Japan/Food. This is part of the program so I can count the number of / in the query/filter string. I jsut want to exclude the extra matches. Any thoughts? Thanks Jon Not sure I would do it using regexps. Unless I misunderstand what you're trying to do, I would consider using LIKE as this will often be much faster: SELECT ... WHERE data LIKE TOP/World/Japan/% AND data NOT LIKE TOP/World/Japan/%/% - in essence: select all those records which start with TOP/World/Japan/, then subtract those which contain any further /. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Regexp Help !!!
Thanks ! Glad I asked for some other thoughts on this. I totally over looked this and given how my indexes are setup, this will work much better. Thanks again, Jon -Original Message- From: Carsten H. Pedersen [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 13, 2002 11:26 AM To: Jon Shoberg; Beginners (E-mail) Cc: *MySQL mail list Subject: RE: Regexp Help !!! -Original Message- From: Jon Shoberg [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 13, 2002 4:47 PM To: Beginners (E-mail) Cc: *MySQL mail list; Jon Shoberg Subject: Regexp Help !!! Ok, I have a list/database of words that follows as ... Top ... Top/Arts/Food Top/Arts/Food/Country ... Top/World/America Top/World/Japan Top/World/Japan/Economy Top/World/Japan/Food Top/World/Japan/Food/Country Top/World/Japan/Food/Country/By_Chef ... How can I setup a regexp query/filter such that I can choose the prefix and the number of / in the results? Example: How can I query/filter the list such that I am looking for all Top/World/ matches that have only one more word after them. So my only results from the example about would be Top/World/America and Top/World/Japan. Then I would do the same for Top/World/Japan and get Top/World/Japan/Economy and then Top/World/Japan/Food. This is part of the program so I can count the number of / in the query/filter string. I jsut want to exclude the extra matches. Any thoughts? Thanks Jon Not sure I would do it using regexps. Unless I misunderstand what you're trying to do, I would consider using LIKE as this will often be much faster: SELECT ... WHERE data LIKE TOP/World/Japan/% AND data NOT LIKE TOP/World/Japan/%/% - in essence: select all those records which start with TOP/World/Japan/, then subtract those which contain any further /. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Regexp Help !!!
From the perl side, you could also split on / and then put each element in a hash with the value being a hashref for the next element's hash. - Original Message - From: Jon Shoberg [EMAIL PROTECTED] To: Carsten H. Pedersen [EMAIL PROTECTED]; Beginners (E-mail) [EMAIL PROTECTED] Cc: *MySQL mail list [EMAIL PROTECTED] Sent: Sunday, January 13, 2002 11:36 AM Subject: RE: Regexp Help !!! Thanks ! Glad I asked for some other thoughts on this. I totally over looked this and given how my indexes are setup, this will work much better. Thanks again, Jon -Original Message- From: Carsten H. Pedersen [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 13, 2002 11:26 AM To: Jon Shoberg; Beginners (E-mail) Cc: *MySQL mail list Subject: RE: Regexp Help !!! -Original Message- From: Jon Shoberg [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 13, 2002 4:47 PM To: Beginners (E-mail) Cc: *MySQL mail list; Jon Shoberg Subject: Regexp Help !!! Ok, I have a list/database of words that follows as ... Top ... Top/Arts/Food Top/Arts/Food/Country ... Top/World/America Top/World/Japan Top/World/Japan/Economy Top/World/Japan/Food Top/World/Japan/Food/Country Top/World/Japan/Food/Country/By_Chef ... How can I setup a regexp query/filter such that I can choose the prefix and the number of / in the results? Example: How can I query/filter the list such that I am looking for all Top/World/ matches that have only one more word after them. So my only results from the example about would be Top/World/America and Top/World/Japan. Then I would do the same for Top/World/Japan and get Top/World/Japan/Economy and then Top/World/Japan/Food. This is part of the program so I can count the number of / in the query/filter string. I jsut want to exclude the extra matches. Any thoughts? Thanks Jon Not sure I would do it using regexps. Unless I misunderstand what you're trying to do, I would consider using LIKE as this will often be much faster: SELECT ... WHERE data LIKE TOP/World/Japan/% AND data NOT LIKE TOP/World/Japan/%/% - in essence: select all those records which start with TOP/World/Japan/, then subtract those which contain any further /. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
