Home » SQL & PL/SQL » SQL & PL/SQL » Mail Validation ?! (10g , XP SP2)
icon5.gif  Mail Validation ?! [message #315633] Tue, 22 April 2008 04:44 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I made that table :-

CREATE TABLE employees_oug (
employeeid NUMBER PRIMARY KEY,
employeesname VARCHAR2(20) NOT NULL,
birthdate DATE ,
hiredate DATE DEFAULT SYSDATE ,
email VARCHAR2(60) UNIQUE CHECK ( email LIKE '%@%.%' and ),
salary NUMBER CHECK ( salary > 100 ) );


but as you can see ......... the email column haven't enough constraints that ensure the mails that will insert into it conform with the common format for any mail ... I mean by "common format" that the E-mail Address should be :-

1) No embedded spaces
2) '@' can't be the first character of an email address
3) '.' can't be the last character of an email address
4) There must be a '.' after '@'
5) Only one '@' sign is allowed
6) Domain name should end with at least 2 character extension
7) can't have patterns like '.@' and '..'

so ... what's the suitable constraints for email column ?!
Re: Mail Validation ?! [message #315637 is a reply to message #315633] Tue, 22 April 2008 04:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1. In 10g you can use a regular expression in a check constraint.

Oracle Database 10g Regular Expressions

2. There are different regular expressions you can use to check e-mail adresses, depending on how strict you want the checks to be.

How to Find or Validate an Email Address
Re: Mail Validation ?! [message #315638 is a reply to message #315633] Tue, 22 April 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems to me this has been adressed here a couple of weeks ago, did you search?

Regards
Michel
Re: Mail Validation ?! [message #315674 is a reply to message #315633] Tue, 22 April 2008 06:35 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

yes , I searched but no way Sad

the problem here that I have no idea about "Regular Expressions" ,
so .... is there anyway to fix this problem by normal SQL ?!
Re: Mail Validation ?! [message #315679 is a reply to message #315674] Tue, 22 April 2008 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean "normal"?
Without any built-in function?
Regexp are part of Oracle SQL built-in functions, they are "normal".
It may be possible without it, good exercise to learn SQL functions, try it and post your solution.

Regards
Michel
Re: Mail Validation ?! [message #315709 is a reply to message #315679] Tue, 22 April 2008 08:50 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I'm still insist on existing of non Regular Expressions for my problem ...... because I found solve like what i need but in SQL Server .... in this link :-

http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm

so .... is there any suggests here Question Question Exclamation Exclamation
Re: Mail Validation ?! [message #315711 is a reply to message #315709] Tue, 22 April 2008 09:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you insist, you could take that, translate it to Oracle SQL and post the result.

Especially since the ONLY reason for that mess according to the site is "because T-SQL lacks sophisticated regular expressions".

Laughing

[Updated on: Tue, 22 April 2008 09:07]

Report message to a moderator

Re: Mail Validation ?! [message #315712 is a reply to message #315709] Tue, 22 April 2008 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It may be possible without it, good exercise to learn SQL functions, try it and post your solution.

Don't you want to do it?
I have a solution, so it is possible, search to do it by yourself.

Regards
Michel
Re: Mail Validation ?! [message #315796 is a reply to message #315712] Tue, 22 April 2008 15:57 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I read the whole story:
Quote:
I'm still insist on existing of non Regular Expressions for my problem because I have no idea about "Regular Expressions"
(and don't intend to waste my time on them (but would LOVE if someone solves this for me, urgnt plz!)).
Previous Topic: Referential Integrity
Next Topic: ??????Referential Integrity???????
Goto Forum:
  


Current Time: Sat Dec 03 03:47:57 CST 2016

Total time taken to generate the page: 0.09492 seconds