CODEWITHSUNDEEP
oracle-databasestored-functions
Praveen Kumar
Praveen Kumar

Reputation: 37

Oracle Function to check for a particular Email Domain Name

I want to write a function in Oracle to check for a particular Email domain. like i need to find for gmail for any other email domain it should return Invalid

If i pass first one it should return Valid and for if i pass Second one it should return Invalid.

Upvotes: -1

Views: 201

Answers (2)

Ranagal
Ranagal

Reputation: 317

As simple as it gets:

create or replace function f_validate_domain(i_email_id in varchar2, i_domain_name in varchar2 default 'gmail.com')
return varchar2
is
begin
    return case when instr(i_email_id,'@'||i_domain_name) > 1 then 'Valid' else 'Not Valid' end;
end;


To test:

begin
    dbms_output.put_line(f_validate_domain('[email protected]'));
    dbms_output.put_line(f_validate_domain('[email protected]', 'gmail.com'));
    dbms_output.put_line(f_validate_domain('[email protected]', 'gmail.com'));
end;

Output:

enter image description here

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

you can work on the below regular expression pattern as per your need. The below code is for demonstration purposes.

create function(email varchar2(40)) 
  return number(1)
is
  result number(1) 
begin
   if regexp_like(email, '[a-zA-Z0-9]{3,}@gmail.com$') then
       result := 1;
   else
       result := 0;
   end if;

   return 0;
end;

Upvotes: 0

Related Questions