Verify Email Address in Oracle 8i

[dave]

Im trying to verify that an email address contains the @ symbol and that the username can not be longer than 64 characters and that the domain name/number can not be longer that 64 characters, I have done the basic check with a like constraint of Email_Address Like '%@%.%' but what I need is that the @ symbol can only occur once and I have tried using the INSTR Function but I am not too familiar with how to use it as well I am trying to use the LENGTH Function to check that the length of the string from the start to the @ symbol is less than or equal to 65 characters and I am trying to do the same for the domain name.

Any Help would be much appreciated

Dave

[oraclable]

A short pl/sql function should be the answer:

this function will rturn 1(if good addr) and 0(else):

script:
--------------------------------------
create or replace function checkemail(email varchar2)
return int as
pos int;
username varchar2(100);
domain varchar2(100);
len int(2):=64;
begin
select instr(email,'@') into pos from dual;
if pos <=0 then
return 0;
else
select substr(email,1,pos), substr(email,pos+1) into username,domain from dual;
if ( length(username)>len or length(domain)>len )then
return 0;
else
return 1;

end if;
end if;
end checkemail;
/
--------------------------------
after creating the function:
try some queries like:
select checkemail('ashortname@domain.com')from dual;

and :
select checkemail('averrryverrryverrryverrryverrryverrryv errryverrryverrryverrryname@domain.com')from dual;

and:
select checkemail('noatnamedomain.com')from dual;

and:
select checkemail('ashortname@untreslonguntreslonguntresl onguntreslonguntreslonguntreslongdomain.com') from dual;

feel free to ask anything about it.

Good luck.