Possible to create Oracle regular expression that's able to match NULL?

Question

In Oracle, is it possible to match both a particular string format and an empty string using a single regex?

Something along the lines of:

SELECT 1
  FROM DUAL
 WHERE REGEXP_LIKE('12345', '^([1-5]{5})|NULL$');

SELECT 1
  FROM DUAL
 WHERE REGEXP_LIKE('', '^([1-5]{5})|NULL$');

SELECT 1
  FROM DUAL
 WHERE REGEXP_LIKE(NULL, '^([1-5]{5})|NULL$');

where the 'NULL' portion of the regex is treated as a character class rather than a string literal and all three queries return 1.

I'm writing some data validation routines and would like to store/use just one regex to determine both the format and whether the input string can be empty -- rather than specifying those validation rules seperately for each data element.

Thanks, Jeff

Answer 1

One option : Use

where rexp_like(expr, ''') or (expr is null)

Answer 2

I don't think you can do so directly, but you could use nvl to make null values show up as a blank string or the string 'NULL'.

SELECT 1
  FROM DUAL
 WHERE REGEXP_LIKE(NVL(value, 'NULL'), '^([1-5]{5})|NULL$');