0
I need help changing the Regex below to validate email domains on an ORACLE basis in accordance with RFC 5322, ensuring that the criteria below are met.
Email domain rules to be respected:
- start by letter or digit and have a ratio between 1 and 63 characters.
- may contain upper and lower case letters of the Latin alphabet (A to Z and a to z).
- can contain digits 0 to 9, provided that the domain (top-level) is not composed only of numbers.
- may contain hyphen -, provided that it is neither the first nor the last character, and does not appear consecutive.
- must accept 2 or more characters in the TLD. ([email protected] not valid, but [email protected] is valid).
I found on the internet the regex below that works perfectly and complies with the rules I listed in Javascript. The problem is 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}$
Would someone please help me to make the necessary modifications to this regex work in Oracle SQL?
Thank you very much
Doing a regex that is fully compatible with RFC 5322 is quite complicated: https://emailregex.com/ - either way, you can remove the Lookahead and validate the field size separately, with
LENGTH(andSUBSTRto take the stretch after the@)– hkotsubo
And for the record, this regex accepts
[email protected]: https://regex101.com/r/bo46A6/1/ :-) But anyway, maybe it’s best to read the bank emails and treat them outside, using some language that supports Lookahead (or that has some other lib dedicated to validate emails, since, as you saw, with regex is much more complicated)– hkotsubo
Thank you very much hkotsubo. Following your advice where to perform the test separately, please help to change this regex '@([A-Z0-9]+(-[A-Z0-9]+)*. ){1,8}[A-Z]{2,63}$', so as to accept only one point after every 2 characters? ([email protected] not valid; [email protected] is valid)
– LEOPOLDO
A way would be
@(?:(?:[A-Z0-9]+(?:-[A-Z0-9]+)*){2,}\.){1,8}[A-Z]{2,63}$. But like I said, maybe we should do it outside the bank. Programming languages usually have more resources for this type of validation (either with regex or with libs that validate this). Doing everything in a single regex is complicated, there will always be a "strange" case that she does not take. See some examples here, here, here and here– hkotsubo