Check eMail Address with SQL

UDF - Check eMail Address with SQL.sql

-- UDF - Check eMail Address:
-- Parameters: ParEmail => EMail Address
-- Returns:    0 = Invalid eMail Address / 1 = valid eMail Address
--             In Release 7.5 the new Boolean Datatype could be used for the Return Value
-- Attention:  There might be better regular expression patterns for checking eMail addresses

Create or Replace Function YourSchema.CheckEmail (ParEMail Varchar(256))
       Returns Integer
   Language SQL
   Modifies SQL Data
   Deterministic
   Called On Null Input
   Set Option Dbgview = *Source
Begin
   Declare RegexPattern VarChar(256) CCSID 1208 
                        Constant '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
   Declare Continue Handler For SQLEXCEPTION Return 0;
   
   Return Case When RegexP_Like(Trim(ParEmail), RegexPattern)
               Then 1
               Else 0
          End;                     
End;

Label On Routine YourSchema.CheckEmail(Varchar()) Is 'UDF - Check eMail Address';

Comment On Parameter Routine YourSchema.Checkemail (Varchar())
   (Paremail Is 'EMailAddress');
   
-- Test
Values(CheckEMail('Manfred.Mustermann@Company.com'));
Values(CheckEMail('Manfred!Mustermann@Company.com'));