Home » SQL & PL/SQL » SQL & PL/SQL » Alter Regex to validate email Domain on RFC 5322 from Java to Oracle sql (Oracle 11g, windows 10)
Alter Regex to validate email Domain on RFC 5322 from Java to Oracle sql [message #679789] Fri, 27 March 2020 05:46 Go to next message
Nicha
Messages: 20
Registered: March 2020
Junior Member
I need to alter the *below Regex* to validate emails domains in a oracle database, according to RFC 5322, and ensuring that the criteria below are respected as well, and that ORACLE does not support look-ahead/-behind.

'@(([A-Z0-9]{1,63})[A-Z0-9]+(-[A-Z0-9]+)*\.){1,8}[A-Z]{2,63}$'

Domain rules that must be respected:

1. must start an end with a letter or digit and be between 1 and 63 characters long.
2. may contain uppercase and lowercase Latin letters (A to Z and a to z).
3. may contain digits 0 to 9, provided that top-level domain names are not all-numeric.
4. may contain hyphen -, provided that it is not the first or last character, and not consecutive also.
5. must have at least 2 or more characters (abc@t.com is not valid, but abc@tt.com is valid).

The above regex is already guaranteeing the rules (2,3,4 and 5).

What do I need?
So, besides guaranteeing rule 1, I must ensure that domains like '@t-online.de' or '@a-bandeira.pt' are valid too. But now it's only validating when the hyphen is after the 2nd character, like '@to-nline.de' or '@ab-andeira.pt'.

I found on the internet the regex below, and all tests went very well. the regex work's very well on the this simulator. Works in Javascript but not in ORACLE, that doesn't support lookahead's.

@(?:(?=[A-Z0-9-]{1,63}\.)[A-Z0-9]+(?:-[A-Z0-9]+)*\.){1,8}[A-Z]{2,63}$

So, can anyone please help me on making the necessary modifications in order to work in Oracle sql?
Re: Alter Regex to validate email Domain on RFC 5322 from Java to Oracle sql [message #679809 is a reply to message #679789] Mon, 30 March 2020 09:46 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Yes, ORACLE doesn't support lookahead's. I have no complete solution, but would split the email in the three main parts and validate them in another step:

WITH emails (ead) AS
  (SELECT 'brucewayne.1981@gmail.com' FROM dual UNION ALL
   SELECT 'clark_kent@gmail.com'      FROM dual UNION ALL
   SELECT 'abc@t-online.de'           FROM dual UNION ALL
   SELECT 'abc@a-bandeira.pt'         FROM dual UNION ALL
   SELECT 'abc@ba-bandeira.pt'        FROM dual UNION ALL
   SELECT 'abc'                       FROM dual UNION ALL
   SELECT '1Tonystark.1980@gmail.com' FROM dual UNION ALL
   SELECT 'Abc@t.com'                 FROM dual UNION ALL
   SELECT 'abc@Tt.com'                FROM dual UNION ALL
   SELECT 'xyz@tt.Com'                FROM dual UNION ALL
   SELECT 'aaa@tt.com'                FROM dual
  )
SELECT ead  
 , regexp_replace(ead, '([^[:cntrl:]]+)@([^[:cntrl:]]+)\.([[:alpha:]]*)$','\1') beg
 , regexp_replace(ead, '([^[:cntrl:]]+)@([^[:cntrl:]]+)\.([[:alpha:]]*)$','\2') ext
 , regexp_replace(ead, '([^[:cntrl:]]+)@([^[:cntrl:]]+)\.([[:alpha:]]*)$','\3') tld
  FROM emails;

EAD                     	BEG		EXT 		TLD 
brucewayne.1981@gmail.com	brucewayne.1981	gmail		com
clark_kent@gmail.com		clark_kent	gmail		com
abc@t-online.de			abc		t-online	de
abc@a-bandeira.pt		abc		a-bandeira	pt
abc@ba-bandeira.pt		abc		ba-bandeira	pt
abc				abc		abc		abc
1Tonystark.1980@gmail.com	1Tonystark.1980	gmail		com
Abc@t.com			Abc		t		com
abc@Tt.com			abc		Tt		com
xyz@tt.Com			xyz		tt		Com
aaa@tt.com			aaa		tt		com
There are many links in the web for this problem too...

[Updated on: Mon, 30 March 2020 09:48]

Report message to a moderator

Re: Alter Regex to validate email Domain on RFC 5322 from Java to Oracle sql [message #679810 is a reply to message #679809] Mon, 30 March 2020 12:41 Go to previous message
Nicha
Messages: 20
Registered: March 2020
Junior Member
Thank you _Jum for your sugestion.

[Updated on: Mon, 30 March 2020 12:48]

Report message to a moderator

Previous Topic: Joined two tables on one row
Next Topic: Fill in records where date missing in sequence
Goto Forum:
  


Current Time: Thu Mar 28 10:28:49 CDT 2024