Issues with using function to validaate a value using oracle regex_like

Question

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

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
I also would like to know the root cause why the above if statement fails.

Any help on this is much appreciated.

At first sight, at least one of the reasons is the `?=` stuff. Oracle regular expressions are based mostly on the POSIX dialect, and in particular it does not support lookarounds. With that said - do you just want to know why your attempt doesn't work, or do you also want a solution that is valid in Oracle? — , Mar 04 '22 at 19:59
If you want help with a valid solution, you need to explain your requirement further, since your attempted regexp does more than just requiring all characters to be alphanumeric with at least one digit and one letter. For example, it requires the length to be at least ten; and who knows what else. State the complete problem exactly as you need to solve it. — , Mar 04 '22 at 20:02
Please note that ranges like `A-z` may or may not be what you need. What's included depends on language settings; for example it may include accented characters. Moreover, even with all ASCII characters, if your `nls_sort` is set to `binary` (which is very common), you will also get some punctuation marks in that range, since in the ASCII table the letter `a` doesn't come right after `Z` - there are six punctuation marks between them. Please state exactly what you mean by "Alphabet" - any ASCII letter, upper or lower case, and nothing else? — , Mar 04 '22 at 21:56

score 3 · Accepted Answer · answered Mar 04 '22 at 20:11

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]')
)

score 0 · Answer 2 · answered Mar 04 '22 at 21:13

Instead of unsupported positive lookaheads use

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

EXPLANATION

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

Issues with using function to validaate a value using oracle regex_like

2 Answers2