CODEWITHSUNDEEP
regexp-likeoracle21c
NoBullMan
NoBullMan

Reputation: 2184

Oracle REGEXP_LIKE fails to recognize valid input

I was looking for a way to validate US ZIP codes and got an answer here. I tested it using regex101 site and confirmed.

However, when I use the same expression in an Oracle stored procedure and pass the exact same input, it fails to recognize it.

The regular expression checks if input is 5 or 9 digits, or 5 digits + dash + 4 digits, and does not start with 3 or more zeros.

^(?!000)\d{5}(-\d{4})?$

Using input 08731-5107, it passes regex test, but fails in this Oracle query:

IF REGEXP_LIKE(LVPOSTCODE, '^(?!000)\d{5,9}$') OR REGEXP_LIKE(LVPOSTCODE, '^(?!000)\d{5}(-\d{4})?$') THEN ...

where LVPOSTCODE is 08731-5107.

Can someone tell me if I am doing something wrong for the above to fail?

Upvotes: 0

Views: 27

Answers (1)

psaraj12
psaraj12

Reputation: 5072

Oracle database supports only POSIX standard of regex.Hence you have to convert your regex to POSIX standard OR With inputs from this answer

IF you have java installed in your database you can use the below

    CREATE AND COMPILE JAVA SOURCE NAMED RegexpMatch AS
    import java.util.regex.Pattern;

    public class RegexpMatch {
      public static int match(
        final String value,
        final String regex
      ){
        final Pattern pattern = Pattern.compile(regex);

        return pattern.matcher(value).matches() ? 1 : 0;
      }
    }

Then wrap it in an SQL function

    CREATE FUNCTION regexp_java_match(value IN VARCHAR2, regex IN VARCHAR2) RETURN NUMBER
    AS LANGUAGE JAVA NAME 'RegexpMatch.match( java.lang.String, java.lang.String ) return int';
    /

And use like the below

IF regexp_java_match(LVPOSTCODE, '^(?!000)\d{5,9}$')=1
then
 --do processing
 end if;

Upvotes: 0

Related Questions