-
Verify Email Address in Oracle 8i
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|