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  |
 |
Nicha
Messages: 34
Registered: March 2020
|
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  |
_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
[Updated on: Mon, 30 March 2020 09:48] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri May 16 23:28:59 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
