regexp like - Oracle REGEXP_LIKE fails to recognize valid input - Stack Overflow

admin管理员组

文章数量:1122846

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?

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?

• regexp-like
• oracle21c

Share Improve this question Follow asked Nov 21, 2024 at 17:15 NoBullManNoBullMan 2,17666 gold badges5050 silver badges106106 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;

本文标签： regexp likeOracle REGEXPLIKE fails to recognize valid inputStack Overflow