Forum Stats

  • 3,853,332 Users
  • 2,264,206 Discussions
  • 7,905,324 Comments

Discussions

Email Address Validation

shiva887
shiva887 Member Posts: 51 Blue Ribbon
edited Oct 28, 2016 2:34PM in SQL & PL/SQL

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('[email protected];@n.com', '[^;]+', 1, LEVEL)), ' ') EMAIL_ID    FROM DUAL  CONNECT BY LEVEL <= REGEXP_COUNT(RTRIM('[email protected];@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--------------      -[email protected]        [email protected]         N

Valid email addresses from wiki link

[email protected]

[email protected]

[email protected]

[email protected]

[email protected] (one-letter local-part)

"much.more unusual"@example.com

"[email protected]"@example.com

"very.(),:;<>[]\".VERY.\"[email protected]\\ \"very\".unusual"@strange.example.com

[email protected]

[email protected] (local domain name with no TLD)

#!$%&'*+-/=?^_`{}|[email protected]

"()<>[]:,;@\\\"!#$%&'-/=?^_`{}| ~.a"@example.org

" "@example.org (space between the quotes)

[email protected] (sent from localhost)

[email protected] (see the List of Internet top-level domains)

[email protected]

[email protected] (although ICANN highly discourages dotless email addresses)

[email protected][IPv6:2001:DB8::1]

Tagged:

Answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Oct 28, 2016 1:51PM
    shiva887 wrote:Also , does oracle have any in-built function to check for email address valdation ?

    No it does not

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Oct 28, 2016 2:34PM

    My go to method for validating an email address is to send it an email with a link, the link gets clicked to confirm there is a recipient.

    You might want to ensure there's an "@" in the address first.

    Or you could just only accept the standard email addresses, any failure in validation could require manual acceptance by a human or the first method. Why both writing a complex regexp that no one can maintain, bug check and might not really be required.

This discussion has been closed.