RegEx which is working in Java is not working in Oracle script

user_18279941 0
Viewed 50

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.

plsql oracle regex
2 Answers

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
user_18632290

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 '%000000%'
AND value NOT LIKE '%111111%'
AND value NOT LIKE '%222222%'
AND value NOT LIKE '%333333%'
AND value NOT LIKE '%444444%'
AND value NOT LIKE '%555555%'
AND value NOT LIKE '%666666%'
AND value NOT LIKE '%777777%'
AND value NOT LIKE '%888888%'
AND value NOT LIKE '%999999%'
THEN
  ...
END IF;

As:

  • TRANSLATE( value, 'X0123456789-', 'X') IS NULL checks that the string only contains numeric or hyphen characters.
  • LENGTH(REPLACE(value, '-')) = 14 checks that the digit string is exactly 14 characters in length.
  • LENGTH(value) <= 17 checks that the total length is 17 or less and so there can be at most 3 hyphens.
  • value NOT LIKE '%--%' checks that the hyphens are separated.
  • value NOT LIKE '%000000%' (etc.) checks that there are not more than 5 continuous repetitive digits.

If you did want to use regular expressions then:

IF  REGEXP_LIKE( value, '^\d+(-\d+){0,3}$')
AND LENGTH(REPLACE(value, '-')) = 14
AND NOT REGEXP_LIKE(value, '(\d)\1{5}')
THEN
  ...
END IF;
user_15092640
Related