| REGEXP [message #530836] |
Fri, 11 November 2011 00:40  |
muralikri
Messages: 638
Registered: August 2011
Location: chennai
|
Senior Member |
|
|
Hello,
I am checking below email's valid or not.
Per below regexp,it should get all emails in the table and now it's getting 2 records.
regexp we re checking below conditions:
1. Username can be a-A, z-Z, 0-9
2. Leading and Trailing White spaces / Quotations ( ' ) / dot ( . ) ignored.
3. Underscore ( _ ) dot ( . ) and single quote ( ' ) are allowed, It should be middle of the username.
4. There should be only one @.
5. There should be no consecutive dots in the domain name.
select email
from test
where REGEXP_LIKE(email,'^[a-zA-Z0-9]+[\w\.\''\_\-]*[a-zA-Z0-9]+[\w\.\''\_\-]*@[a-zA-Z0-9]+[\w\.\''\_\-]*[a-zA-Z0-9]\.[a-zA-Z]+[a-zA-Z\.]*[a-zA-Z]$')
OUTPUT:
murali@gmail.com
n.meisel@stadtwerke-wf.de
Insert into TEST (EMAIL) Values ('gerd.meier@kubus-it.de');
Insert into TEST (EMAIL) Values ('edv@diakonie-sh.de');
Insert into TEST (EMAIL) Values ('murali@gmail.com');
Insert into TEST (EMAIL) Values ('n.meisel@stadtwerke-wf.de');
COMMIT;
Please help me..
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP [message #530852 is a reply to message #530836] |
Fri, 11 November 2011 01:45  |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
There is a problem in the pattern [\w].
It doesn't match any word character as (from me ?!) expected, but only a letter 'w'. See the simplified example below:
WITH data AS
(SELECT 'gerd.meier@kubus-it.de' email FROM dual UNION ALL
SELECT 'edv@diakonie-sh.de' FROM dual UNION ALL
SELECT 'murali@gmail.com' FROM dual UNION ALL
SELECT 'n.meisel@stadtwerke-wf.de' FROM dual)
SELECT email FROM data
WHERE REGEXP_LIKE(email,'^.*@.*\-[\w][a-Z]+\.de$');
EMAIL
-------------------------
n.meisel@stadtwerke-wf.de
WITH data AS
(SELECT 'gerd.meier@kubus-it.de' email FROM dual UNION ALL
SELECT 'edv@diakonie-sh.de' FROM dual UNION ALL
SELECT 'murali@gmail.com' FROM dual UNION ALL
SELECT 'n.meisel@stadtwerke-wf.de' FROM dual)
SELECT email FROM data
WHERE REGEXP_LIKE(email,'^.*@.*\-[w][a-Z]+\.de$') ;
EMAIL
-------------------------
n.meisel@stadtwerke-wf.de
WITH data AS
(SELECT 'gerd.meier@kubus-it.de' email FROM dual UNION ALL
SELECT 'edv@diakonie-sh.de' FROM dual UNION ALL
SELECT 'murali@gmail.com' FROM dual UNION ALL
SELECT 'n.meisel@stadtwerke-wf.de' FROM dual)
SELECT email FROM data
WHERE REGEXP_LIKE(email,'^.*@.*\-\w[a-Z]+\.de$');
EMAIL
-------------------------
gerd.meier@kubus-it.de
edv@diakonie-sh.de
n.meisel@stadtwerke-wf.de
|
|
|
|
|
|
| Re: REGEXP [message #530854 is a reply to message #530853] |
Fri, 11 November 2011 02:23 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Tested the script with.
Oracle 10.2.0.4.0 - 64bit
Oracle 11.1.0.7.0 - 64bit
-> same result - no error
NLS_SORT is set to GERMAN
NLS_SORT=BINARY could give ORA-12728
[Updated on: Fri, 11 November 2011 02:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP [message #530898 is a reply to message #530897] |
Fri, 11 November 2011 05:13 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Because of the problem with '\w' You should use '[:alnum:]' instead see REG_EXP . So Your first SELECT works:
WITH data AS
(SELECT 'gerd.meier@kubus-it.de' email FROM dual UNION ALL
SELECT 'edv@diakonie-sh.de' FROM dual UNION ALL
SELECT 'murali@gmail.com' FROM dual UNION ALL
SELECT 'n.meisel@stadtwerke-wf.de' FROM dual)
SELECT email FROM data
WHERE REGEXP_LIKE(email,'^[a-zA-Z0-9]+[[:alnum:]\.\''\_\-]*[a-zA-Z0-9]+[[:alnum:]\.\''\_\-]*@[a-zA-Z0-9]+[[:alnum:]\.\''\_\-]*[a-zA-Z0-9]\.[a-zA-Z]+[a-zA-Z\.]*[a-zA-Z]$');
EMAIL
-------------------------
gerd.meier@kubus-it.de
edv@diakonie-sh.de
murali@gmail.com
n.meisel@stadtwerke-wf.de
[Updated on: Fri, 11 November 2011 05:15] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP [message #530919 is a reply to message #530903] |
Fri, 11 November 2011 06:06 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Why do You use a very complicated regular expression, that You (I suspect) don't understand?
WITH data AS
(SELECT 'gerd.meier@kubus-it.de' email FROM dual UNION ALL
SELECT 'edv@diakonie-sh.de' FROM dual UNION ALL
SELECT 'murali@gmail.com' FROM dual UNION ALL
SELECT 'n.meisel@stadtwerke-wf.de' FROM dual)
SELECT email FROM data
WHERE regexp_like(email,
'[a-z0-9!#$%&''*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+[a-z0-9]([a-z0-9-]*[a-z0-9])?');
EMAIL
-------------------------
gerd.meier@kubus-it.de
edv@diakonie-sh.de
murali@gmail.com
n.meisel@stadtwerke-wf.de
This pattern will match all emails, but I didn' check all parts of the pattern...
|
|
|
|
|
|
|
|
| Re: REGEXP [message #530933 is a reply to message #530929] |
Fri, 11 November 2011 06:44 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hello, again, Muraliki. It looks as though you have no intention on commenting on my suggestion that you should try UTL_SMTP.VRFY instead of these awful REGEXP techniques. But in case anyone else is interested, here's a way of testing not only if an address is usable, but also whether it actually exists. set serveroutput on
declare
c utl_smtp.connection;
r utl_smtp.reply;
begin
c := UTL_SMTP.OPEN_CONNECTION('orafaq.com');
UTL_SMTP.HELO(c, 'foo.com');
r := utl_smtp.vrfy(c,'muraliki@orafaq.com');
dbms_output.put_line(r.code||' '||r.text);
end;
/
Works beautifully. Of course, the response code you get back will depend on whether the adderss is recognized and how the smtp server is configured.
|
|
|
|
|
|
| Re: REGEXP [message #530935 is a reply to message #530934] |
Fri, 11 November 2011 06:57 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:i am not able undetstand..how can i used this code...
1. Run it.
2. Substitute real addresses.
3. Re-write and debug to fit your needs.
4. Say "thank you" to me and everyone else who has tried to assist you.
|
|
|
|
|
|
|
|
| Re: REGEXP [message #530940 is a reply to message #530937] |
Fri, 11 November 2011 07:24 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, here's another example.orcl> set serveroutput on
orcl>
orcl> declare
2 c utl_smtp.connection;
3 r utl_smtp.reply;
4
5 begin
6 c := UTL_SMTP.OPEN_CONNECTION('smtp.gmail.com');
7 UTL_SMTP.HELO(c, 'foo.com');
8 r := utl_smtp.vrfy(c,'john.smith@gmail.com');
9 dbms_output.put_line(r.code||' '||r.text);
10 end;
11 /
252 2.1.5 Send some mail, I'll try my best i8sm6875581wie.11
PL/SQL procedure successfully completed.
orcl>
orcl>
|
|
|
|
|
|
| Re: REGEXP [message #530949 is a reply to message #530941] |
Fri, 11 November 2011 07:57 |
 |
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
muralikri wrote on Fri, 11 November 2011 14:35Thanks lot, good help..
Help? You want to say thanks to do ly job.
This is NOT just help.
You try nothing of what we told you, you just wanted we do your work and waste many's time.
As I told you, in addition to John function, we already gave a COMPLETE solution, but you are too lazy to search it.
Regards
Michel
[Updated on: Fri, 11 November 2011 08:01] Report message to a moderator |
|
|
|
|
|
|
|
| Re: REGEXP [message #573421 is a reply to message #573418] |
Fri, 28 December 2012 05:15 |
 |
sss111ind
Messages: 636
Registered: April 2012
Location: India
|
Senior Member |
|
|
Hi Michel,
I got from your post only.Thanks.
A complete email validation string is:
"^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
Regards,
Nathan
[Updated on: Fri, 28 December 2012 06:00] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
