All , i have the below query in my applicaton which i use to check for valid and invalid email addresses. The input string can contain multiple emails addresses saperated by a semi colon ';'
The regular expression covers most of the valid emails untill someone pointed me towards the wiki link (https://en.wikipedia.org/wiki/Email_address ) where the list of valid emails are showing as invalid in my query . Bascially from my observation from the list what i do see is that any set of characters enclosed in double quotes ' " ' is considered a valid email address which includes characters like @ and ; also (copied the list below). Also , DNS name (.com or any other) are also not mandatory as per wiki.
Can someone please help me come up with a regular expression or any other approach to include all valid cases.
Also , does oracle have any in-built function to check for email address valdation ?
WITH EMAIL_ADDRESS AS
(SELECT NVL(TRIM(REGEXP_SUBSTR('a@b.com;@n.com', '[^;]+', 1, LEVEL)), ' ') EMAIL_ID
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(RTRIM('a@b.com;@n.com', ';'), ';') + 1)
SELECT EMAIL_ID,
(CASE
WHEN REGEXP_INSTR(LOWER(EMAIL_ID),
'^[[:alnum:]'']+((\&|\.|\+|\-|_)[[:alnum:]'']+)?+@[[:alnum:]-]+((\.[a-z]{2,}){1,4})$') = 1 THEN
'Y'
ELSE
'N'
END) IS_VALID_EMAIL
FROM EMAIL_ADDRESS;
EMAIL_ID I
-------------- -
a@b.com Y
@n.com N
Valid email addresses from wiki link
prettyandsimple@example.com
very.common@example.com
disposable.style.email.with+symbol@example.com
other.email-with-dash@example.com
x@example.com (one-letter local-part)
"much.more unusual"@example.com
"very.unusual.@.unusual.com"@example.com
"very.(),:;<>[]\".VERY.\"very@\\ \"very\".unusual"@strange.example.com
example-indeed@strange-example.com
admin@mailserver1 (local domain name with no TLD)
#!$%&'*+-/=?^_`{}|~@example.org
"()<>[]:,;@\\\"!#$%&'-/=?^_`{}| ~.a"@example.org
" "@example.org (space between the quotes)
example@localhost (sent from localhost)
example@s.solutions (see the List of Internet top-level domains)
user@localserver
user@tt (although ICANN highly discourages dotless email addresses)
user@[IPv6:2001:DB8::1]