Email validation for Apex application text item field

Question

I have an Apex application text item to enter the email ids with comma separator.

Now I want to validate the email address whether it's correct or not which entered in application item.

How to achieve this?

I am using the below code but its not working.

declare
  l_cnt             varchar2(1000);
  l_requestors_name varchar2(4000);
begin
  select apex_item.text(1) Member 
    into l_requestors_name 
    from dual;

  if not l_requestors_name not like '%@%' then
     return true;
  else
     return false;
  end if;
end;

Littlefoot Littlefoot · Accepted Answer · 2018-05-16T20:56:54

I'd suggest you to create a function which returns a Boolean or - as in my example - a character (Y - yes, it is valid; N - no, it isn't valid) (why character? You can use such a function in SQL. Boolean works in PL/SQL, but my example is pure SQL).

I guess it isn't perfect, but should be way better than just testing whether a string someone entered contains a monkey (@).

SQL> create or replace
  2     function f_email_valid (par_email in varchar2)
  3        return varchar2
  4     is
  5     begin
  6        return
  7          case when regexp_substr (
  8                      par_email,
  9                      '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')
 10                      is not null
 11                    or par_email is null then 'Y'
 12               else 'N'
 13          end;
 14     end f_email_valid;
 15  /

Function created.

SQL>

As user can enter several e-mail addresses separated by a comma, you'll have to split them into rows and then check each of them. Have a look:

SQL> with test (text) as
  2    -- sample data
  3    (select '[email protected],bigfootyahoo.com,a@@hotmail.com,[email protected]' from dual),
  4  split_emails as
  5    -- split that long comma-separated values column into rows
  6    (select regexp_substr(text, '[^,]+', 1, level) email
  7     from test
  8     connect by level <= regexp_count(text, ',') + 1
  9    )
 10  -- check every e-mail
 11  select email, f_email_valid(email) is_valid
 12  from split_emails;

EMAIL                          IS_VALID
------------------------------ --------------------
[email protected]           Y
bigfootyahoo.com               N
a@@hotmail.com                 N
[email protected]               Y

SQL>

Email validation for Apex application text item field

1 Answers