• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1048
  • Last Modified:

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
0
wendelina
Asked:
wendelina
2 Solutions
 
momi_sabagCommented:
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 '_%@%@%'
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
0
 
tliottaCommented:
Wendy:

Some back ground --

http://www.faqs.org/rfcs/rfc2822.html
http://www.faqs.org/rfcs/rfc822.html

RFC2822 somewhat obsoletes the earlier RFC822. It shouldn't take much skimming of either of those to get scared off of trying to 'validate' an e-mail address. Note that [ wendy@some@email.co.co ] is not valid, but [ wendy(@some)@email(.co).co ] _is_ valid. Here are some quick pages to fill in what the RFCs obscure:

http://www.oreillynet.com/onlamp/blog/2002/12/how_to_validate_an_email_addre.html
http://www.coveryourasp.com/ValidateEmail.asp
http://www.regular-expressions.info/email.html

The last one gives a regular-expression that covers a LOT of 'normal' e-mail addresses and might be sufficient for your needs. The middle one mentions a couple "tests" that mean you would call DNS APIs and write sockets functions to communicate with remote SMTP servers. The first link, though, is a gem.

If you read those few paragraphs and accept them as true (they are), you'll see what's ahead of you.

If you really want to continue after that, post back here.

Tom
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dave FordSoftware Developer / Database AdministratorCommented:

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

0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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

0
 
tliottaCommented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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

SET emailAddress = TRIM(emailAddress);
0
 
wendelinaAuthor Commented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

OK, I admit it. I'm a full-fledged geek. I actually thought this was kind of fun, so I enhanced the funtion slightly.  Check it out.

HTH,
DaveSlash

/*-------------------------------------------------------------*/
/* Procedure: ValidateEmail                                    */
/*-------------------------------------------------------------*/
/* Programmer :  Dave                                          */
/* Date       :  2008-01-06                                    */
/* Logic      :  A valid email address must meet the following */
/*               criteria:                                     */
/*     - Cannot be length < 5 (e.g. a@b.c)                     */
/*     - Must contain an @ sign located between postion 2 and  */
/*       length(emailAddress) - 3                              */
/*     - Must contain a period located between the position of */
/*       the @ sign ( + 2 ) and length(emailAddress) - 1       */
/*-------------------------------------------------------------*/
drop function ValidateEmail;
 
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;                                                        
 
 SET emailAddress = TRIM(emailAddress);                         
 
 -- Initialize the scratch variables                            
 SET atPosition = locate('@', emailAddress);                    
 SET stringLength = LENGTH(emailAddress);
 
 -- notice that periodPosition is the position of the first period
 -- AFTER the position of the @-sign                              
 SET periodPosition = locate('.', emailAddress, atPosition + 1);
 
 if ((stringLength < 5)   or                        
     (periodPosition < 2) or                        
     (periodPosition + atPosition = stringLength) or
     (atPosition < 2) or                            
     (atPosition > stringLength - 3)) then          
        return 0;                                   
 end if;                                            
 
 RETURN 1;                                    
 
END
 
 
Sample results
--------------
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@verzon.com
            0 
            0    @ .
            0   dave@.com

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now