I have to validate a string against some rule. They are:

1. Input can have optional hyphens but 3 hyphens at maximum.
2. Hyphens should not be counted in length.
3. The length should be exactly 14 digits.
4. The string has to be numeric.
5. The string shouldn't contain more than 5 continuous repetitive digits.

My regular expression which is working as expected in Java is

^(?!.*?(\\d)\\1{5})(?=(?:[0-9]-?){14}$)[0-9] (?:-[0-9] ){0,3}$

I am trying to implement the same logic in the oracle script like below

IF(REGEXP_LIKE(<myInput>,'(?=(?:[0-9]-?){14}$)') 
AND NOT REGEXP_LIKE(<myInput>,'([0-9])(\1){5}') 
AND REGEXP_LIKE(<myInput>,'^[0-9] (?:-[0-9] ){0,3}$')) 
THEN ....
END IF;

Regular Expression to identify more than 5 continuous repetitive digits is working properly but (?=(?:[0-9]-?){14}$) and ^[0-9] (?:-[0-9] ){0,3}$ are not working as expected.

Am I missing anything here? I tried to keep/remove brackets,start-line, and end-line anchors around the expressions but no luck.

CodePudding user response：

Oracle regex does not support lookarounds. We can try enforcing your logic via several different checks.

WHERE myInput NOT LIKE '%-%-%-%-%' AND            -- 3 hyphens maximum
      LENGTH(REPLACE(myInput, '-', '')) = 14 AND  -- length 14
      REGEXP_LIKE(myInput, '^[0-9-] $') AND       -- digits   hyphen only
      NOT REGEXP_LIKE(myInput, '[0-9]{6,}')       -- max 5 consecutive digits

CodePudding user response：

Oracle regular expressions do not support positive- or negative-lookahead or non-capturing groups so you need to perform multiple checks for the different tests rather than trying to do it all in one regular expression.

You can do it without (slow) regular expressions using:

IF  TRANSLATE( value, 'X0123456789-', 'X') IS NULL
AND LENGTH(REPLACE(value, '-')) = 14
AND LENGTH(value) <= 17
AND value NOT LIKE '%--%'
AND value NOT LIKE '0000%'
AND value NOT LIKE '1111%'
AND value NOT LIKE '"2222%'
AND value NOT LIKE '33333%'
AND value NOT LIKE 'D4444%'
AND value NOT LIKE 'U5555%'
AND value NOT LIKE 'f6666%'
AND value NOT LIKE 'w7777%'
AND value NOT LIKE '