Home » SQL & PL/SQL » SQL & PL/SQL » validating mail id (oracle 9i)
validating mail id [message #324836] Wed, 04 June 2008 05:16 Go to next message
raffee.v
Messages: 12
Registered: May 2008
Junior Member
suppose mail id is raffee.v@gmail.org>@com.co.in

it is not valid bcz there are 2 @ symbols

i need this type of validations 2 check mail id is correct or not
Re: validating mail id [message #324837 is a reply to message #324836] Wed, 04 June 2008 05:17 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
google is your friend.

http://www.google.co.uk/search?hl=en&q=validate+e-mail+address&meta=
http://www.regular-expressions.info/email.html

Regards

Raj
Re: validating mail id [message #324838 is a reply to message #324837] Wed, 04 June 2008 05:22 Go to previous messageGo to next message
raffee.v
Messages: 12
Registered: May 2008
Junior Member
i also know searching in google.i need complete pl/sql code that all possible validations for mail id
Re: validating mail id [message #324839 is a reply to message #324836] Wed, 04 June 2008 05:26 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
I hope this will help you
DECLARE
	v_value varchar2(30) := 'example.example@example.com';
BEGIN
	IF owa_pattern.match(v_value, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{3}$') THEN
		DBMS_OUTPUT.PUT_LINE('valid');
	ELSE
		DBMS_OUTPUT.PUT_LINE('invalid');
	end if;
END;
/
Re: validating mail id [message #324843 is a reply to message #324838] Wed, 04 June 2008 05:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you know how to search in google then why don't you try what is available to you and come back to us only when you have some problems rather than simply posting I want to validate xyz. Please help me.

Regards

Raj
Re: validating mail id [message #325224 is a reply to message #324839] Thu, 05 June 2008 05:46 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi Sarwagya,

I tried your solution but did not get the correct answer. Can you please let me know if I have interpreted the code properly.

DECLARE
	a varchar2(100) := 'a@yahoo.com';
BEGIN
	IF owa_pattern.match(v_value, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{3}$') THEN
		DBMS_OUTPUT.PUT_LINE('valid');
	ELSE
		DBMS_OUTPUT.PUT_LINE('invalid');
	end if;
END;
/


Quote:
INVALID

PL/SQL procedure successfully completed.


Can you tell me why this might not be working?

- Das



Re: validating mail id [message #325225 is a reply to message #325224] Thu, 05 June 2008 05:48 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
DECLARE
	a varchar2(100) := 'a@yahoo.com';
BEGIN
	IF owa_pattern.match(a, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{3}$') THEN
		DBMS_OUTPUT.PUT_LINE('valid');
	ELSE
		DBMS_OUTPUT.PUT_LINE('invalid');
	end if;
END;
/


sorry...here
Re: validating mail id [message #325231 is a reply to message #325224] Thu, 05 June 2008 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you tell me why this might not be working?

What is your opinion or analyze?

SQL> DECLARE
  2   a varchar2(100) := 'a@yahoo.com';
  3  BEGIN
  4   IF owa_pattern.match(a, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{3}$') THEN
  5    DBMS_OUTPUT.PUT_LINE('valid');
  6   ELSE
  7    DBMS_OUTPUT.PUT_LINE('invalid');
  8   end if;
  9  END;
 10  /
valid

PL/SQL procedure successfully completed.


Regards
Michel

[Updated on: Thu, 05 June 2008 05:53]

Report message to a moderator

Re: validating mail id [message #325233 is a reply to message #325231] Thu, 05 June 2008 06:03 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
oopss...brackets made all the difference

Thanks

Re: validating mail id [message #325240 is a reply to message #325233] Thu, 05 June 2008 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The code needs to be a little bit change to support France, for instance:
SQL> DECLARE
  2   a varchar2(100) := 'a@yahoo.fr';
  3  BEGIN
  4   IF owa_pattern.match(a, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{3}$') THEN
  5    DBMS_OUTPUT.PUT_LINE('valid');
  6   ELSE
  7    DBMS_OUTPUT.PUT_LINE('invalid');
  8   end if;
  9  END;
 10  /
invalid

PL/SQL procedure successfully completed.

I let you find where.

Regards
Michel
Re: validating mail id [message #325243 is a reply to message #325240] Thu, 05 June 2008 06:27 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
SQL> DECLARE
  2   a varchar2(100) := 'a@yahoo.fr';
  3  BEGIN
  4   IF owa_pattern.match(a, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{2}$') THEN
  5    DBMS_OUTPUT.PUT_LINE('valid');
  6   ELSE
  7    DBMS_OUTPUT.PUT_LINE('invalid');
  8   end if;
  9  END;
 10  /
VALID

PL/SQL procedure successfully completed.


here...
Re: validating mail id [message #325247 is a reply to message #325243] Thu, 05 June 2008 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but now, it no more works for "yahoo.com".

Regards
Michel
Re: validating mail id [message #325248 is a reply to message #325243] Thu, 05 June 2008 06:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Two more cases for people who like to re-invent the wheel instead of reading S.Rajarams second link with an excellent discussion on which regular expression to use for which level of validation : Wink

someone@oracle.co.uk

someone@nutjobs.info
Re: validating mail id [message #325250 is a reply to message #325243] Thu, 05 June 2008 06:43 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Please try to find out as much possibilities
in an email id and modify the regular expression pattern.

I just presented a simple validation code block.

Sarwagya.
Re: validating mail id [message #325253 is a reply to message #325248] Thu, 05 June 2008 06:47 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Yeah,

There is a complete regular exp for validation
Re: validating mail id [message #325260 is a reply to message #325243] Thu, 05 June 2008 07:37 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
durgadas.menon wrote on Thu, 05 June 2008 07:27
SQL> DECLARE
  2   a varchar2(100) := 'a@yahoo.fr';
  3  BEGIN
  4   IF owa_pattern.match(a, '^[a-zA-Z_\.]+@[a-zA-Z_]+\.[a-zA-Z]{2}$') THEN
  5    DBMS_OUTPUT.PUT_LINE('valid');
  6   ELSE
  7    DBMS_OUTPUT.PUT_LINE('invalid');
  8   end if;
  9  END;
 10  /
VALID

PL/SQL procedure successfully completed.




Impossible to get uppercase output from this code. Why can't you just run the real code and show the real output?
Re: validating mail id [message #325261 is a reply to message #325260] Thu, 05 June 2008 07:58 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I tried the same code in SQL*Plus and got green bold letters on black background:

./fa/4407/0/

So, don't say it is impossible. If you wish, I can teach you how to do that. Warning: it isn't simple!
Re: validating mail id [message #325262 is a reply to message #325261] Thu, 05 June 2008 08:01 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I stand corrected, but if it's not simple, I don't think Durgadas' output was based on it.
Re: validating mail id [message #325277 is a reply to message #325261] Thu, 05 June 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you wish, I can teach you how to do that.

Always eager to learn, so please do so.
I remember I was able to do it with Oracle version <6 on DOS but can't remember how I did it.

Regards
Michel
Re: validating mail id [message #325304 is a reply to message #325277] Thu, 05 June 2008 11:43 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
- copy durgadas.menon's code
- paste it into Corel Draw, paint it white
- create black rectangle and put it to background
- select "VALID", make it bold and paint green
- take a screenshot and post it here

Cheating? Maybe, a little bit.

Sorry, shouldn't have done that.
Re: validating mail id [message #325315 is a reply to message #325304] Thu, 05 June 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm disappointed Sad , I remember I could do this (talking about the color) but maybe it was with UFI (SQL*Plus ancestor).

Regards
Michel

[Updated on: Thu, 05 June 2008 13:17]

Report message to a moderator

Re: validating mail id [message #325327 is a reply to message #325315] Thu, 05 June 2008 15:05 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Here's one way:

./fa/4408/0/
  • Attachment: color.PNG
    (Size: 16.86KB, Downloaded 692 times)
Re: validating mail id [message #325369 is a reply to message #325247] Fri, 06 June 2008 00:46 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
yes..it does not so maybe add a couple of "elsif" statements with different combinations should do the trick. This way ID's like someone@oracle.co.uk, someone@nutjobs.info can also be take care of.

...or is there an easier way?
Re: validating mail id [message #325371 is a reply to message #325262] Fri, 06 June 2008 00:48 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Yes you are right...I would have written it manually.

SQL> @d:\test.sql
 11  /
valid

PL/SQL procedure successfully completed.


Previous Topic: Use of Decode in Materialized View
Next Topic: Bug while using Between for Date Fields
Goto Forum:
  


Current Time: Wed Dec 07 18:55:47 CST 2016

Total time taken to generate the page: 0.24957 seconds