CODEWITHSUNDEEP
regexoracle-databaseplsql-packageregexp-like
vr3w3c9
vr3w3c9

Reputation: 1178

Issues with using function to validaate a value using oracle regex_like

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.

Upvotes: 0

Views: 206

Answers (2)

Ryszard Czech
Ryszard Czech

Reputation: 18611

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

Upvotes: 0

MT0
MT0

Reputation: 167867

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

Upvotes: 3

Related Questions