Home › Database › Oracle › Oracle: Email validation

Oracle: Email validation

Posted on Posted in Oracle Tagged with , , ,

A reasonably decent email validation:
select
PRIMARY_EMAIL_ADDRESS DATA,
–Email validation rules determined as per: http://rumkin.com/software/email/rules.php
–This validation checks a reasonably comprehensive subset of real world validations, taken from above.
–For each individual check, 0=PASS|1=FAIL. Sum results of all checks, if result = 0 all checks have passed.
sign
( –chk11: domain part labels should consist of only a-z, A-Z, 0-9, or any of !# $%& ‘ * +/= ? ^_` { | }~ .-
abs(sign(1-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^.*@{1}[A-Z0-9!#\$%&\”\*\+/=\?\^_`\{\|\}~\.-]{1,}$’,1,1,0,’i’)))) +
–chk10: local part labels should consist of only a-z, A-Z, 0-9, or any of !# $%& ‘ * +/= ? ^_` { | }~ .-
abs(sign(1-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^[A-Z0-9!#\$%&\”\*\+/=\?\^_`\{\|\}~\.-]{1,}?@{1}.*$’,1,1,0,’i’)))) +
–chk9: No domain can have two successive periods
abs(sign(0-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^[^@]{1,}?@{1}.*\.\..*$’)))) +
–chk8: No domain can end with a period
abs(sign(0-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^[^@]{1,}?@{1}.*\.$$’)))) +
–chk7: No domain can start with a period
abs(sign(0-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^[^@]{1,}?@{1}\..*$$’)))) +
–chk6: domain consists of labels separated by periods and less than 253 characters.
abs(sign(1-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),'[^@]{1,}?@{1}[^@]{1,253}?$’)))) +
–chk5: No localpart can have two successive periods
abs(sign(0-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^.*\.\..*@{1}[^@]{1,}?$’)))) +
–chk4: No localpart can end with a period
abs(sign(0-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^.*\.@{1}[^@]{1,}?$’)))) +
–chk3: No localpart can start with a period
abs(sign(0-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^\..*$’)))) +
–chk2: The localpart must be 64 characters or less.
abs(sign(1-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^[^@]{1,64}?@{1}[^@]{1,}?$’)))) +
–chk1: The email has a localpart on the left of an @, the domain on the right. Neither the localpart nor the domain may be empty
abs(sign(1-( REGEXP_INSTR(trim(PRIMARY_EMAIL_ADDRESS),’^[^@]{1,}?@{1}[^@]{1,}?$’))))
) AS RESULT
from
TABLE_CONTAINING_EMAIL