regex to find and avoid double dots in Oracle

Question

I am trying to find double dots, if consecutive double dot exits in any email it should prompt me with "NO"

DECLARE 
v_email webowner.person.email%TYPE;
v_constant CONSTANT VARCHAR2(300) := '^(([a-zA-Z0-9"_\-])([a-zA-Z0-9_\.\/%+="''\-]*[a-zA-Z0-9])@(\[((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.){3}|((([a-zA-Z0-9\-]+)\.)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\]))$';
BEGIN
  v_email := '[email protected]';
if regexp_like(v_email, v_constant) then

  pl('YES: ' || v_email);
else
  pl('NO: ' || v_email);
end if;
END;

Note: Double dot means consecutive dots and presence of double dots needs to be checked before "@"

Tried using Regex that does not allow consecutive dots

Could not find the proper placement of regex.

Kindly help.

Are you certain that you even want to allow double dots anywhere ? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 23, 2017 at 11:25
Replace ([a-zA-Z0-9"_\-])([a-zA-Z0-9_\.\/%+="''\-]*[a-zA-Z0-9]) with [a-zA-Z0-9"_-]+(\.[a-zA-Z0-9_/%+="''-]*[a-zA-Z0-9])* — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Oct 23, 2017 at 12:02

Tim Biegeleisen · Accepted Answer · 2017-10-23 12:02:21Z

1

My immediate idea is just to check if the index of ".." is less than the index of "@", and that the first index is not -1.

SELECT
    CASE WHEN INSTR(v_email, '..') BETWEEN 1 AND INSTR(v_email, '@') - 1
         THEN 'dots present'
         ELSE 'no dots' END AS dots
FROM yourTable;

I hope this is of help to you :)

edited Oct 23, 2017 at 12:02

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Oct 23, 2017 at 11:32

Quaisaq Anderson

1086 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jaytea · Accepted Answer · 2017-10-23 11:27:16Z

0

Change this part:

([a-zA-Z0-9_\.\/%+="''\-]*[a-zA-Z0-9])

To this:

((\.?[a-zA-Z0-9_\/%+="''\-]+)*\.?[a-zA-Z0-9])

So basically, if there are any dots to match, they must be interspersed with some number of non-dot, acceptable characters. That way you will never match two consecutive ones.

answered Oct 23, 2017 at 11:27

jaytea

1,9891 gold badge14 silver badges19 bronze badges

1 Comment

Pooja Over a year ago

Request you to look into Question No. 47416137

Collectives™ on Stack Overflow

regex to find and avoid double dots in Oracle

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related