I have a PL/SQL funtion where I validate a field value using Regex.Below is the if statement I have in the PL/SQL function. where Im trying to verify if the Input is alphanumeric with atleast one number and one alphabet

if(regexp_like('AB67868777','^(?=.*[0-9])(?=.*[A-Z])([A-Z0-9]{10} )$')

When the execution reaches the above line, It returns false instead of true. I have verified the regex and it works fine. Have used an online java regular expression validator and tested the above regex script and input param. The result was true. Where as when executing it from the code it returns fall.
Need some inputs to identify why the if statement above in the PL/SQL function is returning false

Help Required

1. My requirement is to validate if the input is Alphanumeric (i.e containing atleast one number(0-9) and one Alphabet(A-z)) and length should be of 10 characters
2. I also would like to know the root cause why the above if statement fails.

Any help on this is much appreciated.

CodePudding user response：

In Oracle, look-ahead (and look-behind) are not supported in regular expressions. You can get the same effect by using REGEXP_LIKE for each separate test:

if(
      regexp_like('AB67868777','^[A-Z0-9]{10} $')
  and regexp_like('AB67868777','[0-9]')
  and regexp_like('AB67868777','[A-Z]')
)

CodePudding user response：

Instead of unsupported positive lookaheads use

if(
      regexp_like('AB67868777','^[[:upper:][:digit:]]{10}$')
  and 
      regexp_like('AB67868777','[[:digit:]]')
  and 
      regexp_like('AB67868777','[[:upper:]]')
)

EXPLANATION

1. ^[[:upper:][:digit:]]{10}$ - string must contain only ten upper letters/digits
2. [[:digit:]] - matches digit
3. [[:upper:]] - matches upper letter