asked on

In RPGIV or DB2, is there a command to "validate" email address format?

Hi,
For our database, we are beginning to store more and more email addresses, and we need a well defined way to know if the email address format is "valid". Is there some sort of a routine or a command that will validate that an email address is or isn't valid, based on the latest set of rules?
for example: wendy@someemail.com is valid, but wendy@some@email.co.co is not valid -
thank you,
Wendy

momi_sabag

i don't know any command that validate emails as you describe
but if you tell me the set of rules you want to validate, we can try to figure something out
maybe you can even define it as a check constraint or enforce it using a trigger

for example, the exmaple you gave could be written as
email_address not like '_%@%@%'

Member_2_2484401

To my knowledge, there is no BUILT-IN function to validate email address. As momi points out, you could define a complex check constraint over the column, but (if it were me) I'd just write user-defined function to do it.

I don't have one already written, but it can't be all that hard if you have the rules already defined.

Let me know if you need an example or two of a UDF.

HTH,
DaveSlash

SOLUTION

Member_2_276102

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_2484401

As far as I can tell, the only valid rules are:

- Cannot be length of 0
- Must contain an @ sign located between postion 2 and length(emailAddress) - 3
- Must contain a period located between position 4 and length(emailAddress) - 1
- Must contain a period AFTER the @ sign

Therefore, the following UDF seems to work for me.

HTH,
DaveSlash

CREATE FUNCTION ValidateEmail (                
  emailAddress VARCHAR(100)                    
)                                              
RETURNS integer                                
LANGUAGE SQL                                   
modifies sql data                              
set option dbgview = *source                   
 
BEGIN
-- Scratch variables used for processing       
DECLARE stringLength INT;                      
DECLARE atPosition INT;                        
DECLARE periodPosition INT;                    
 
 -- If the incoming string is NULL, return NULL
 IF (emailAddress IS NULL) THEN
   return 0;
 End If;
 
 -- Initialize the scratch variables
 SET atPosition = locate('@', emailAddress);
 SET stringLength = LENGTH(emailAddress);
 SET periodPosition = locate('.', emailAddress, atPosition + 1)
 
 if (periodPosition = 0) then
    return 0;
 end if;
 
 set periodPosition = periodPosition + atPosition;
 
 if  (stringLength = 0) then
    return 0;
 end if;
 
 if ((atPosition < 2) or (atPosition > stringLength - 3)) then
    return 0;
 end if;
 
 if ((periodPosition < 4) or (periodPosition > stringLength - 1)) then
    return 0;
 end if;
 
 if (periodPosition < atPosition) then
    return 0;
 end if;
 
 RETURN 1;
 
END

Open in new window

Member_2_2484401

Here's some results from the UDF:

select ValidateEmail(emailAddress),
       emailAddress                
from   deleteme
 
VALIDATEEMAIL   EMAILADDRESS                
            1   a@b.c                       
            1   abcdefghijk@blmnopqrstuv.com
            0   abcdefghijk@blmnopqrstuvcom 
            0   abcdefg.hijk@blmnopqrstuvcom
            0   @bcdefghijkcblmnopqrstuv.com
            0   abcdefg@ijkcblmnopqrstuvxco.
            0   abcd.com                    
            0   abcd@com                    
            1   dave.slash@verizon.com        
 
-- DaveSlash

Open in new window

Member_2_276102

I messed up on my valid example -- [ wendy@some(@)email(.co).com ] was what you should have been shown. That's assuming that your valid example was correct.

Tom

Member_2_2484401

At line 19 above, I found it useful to add the following:

SET emailAddress = TRIM(emailAddress);

wendelina

ASKER

Thank you for all the postings. I will need to take a few hours amongst other "goings on" to sort through them all. I will reply as soon as practical....
Wendy

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

In RPGIV or DB2, is there a command to &quot;validate&quot; email address format?

In RPGIV or DB2, is there a command to "validate" email address format?