How can I make REGEXP_LIKE match an empty string?
511898Sep 4 2009 — edited Sep 6 2009Using only regexp_like (not some OR'd condition), I want to match either a five-digit number or an empty string. I'm using an automated validation tool that only allows me to enter ONLY a column name and a pattern to match. The tool then generates the SQL to validate the column, and I have no opportunity to modify it later. I can't just type in something like COLNAME='' OR REGEXP_LIKE(COLNAME,'^\d{5}$') because the tool won't let me. Or rather, it will generate SQL that won't work.
Some Examples:
This works (i.e. returns a row from dual):
select * from dual where regexp_like('12345','(^$)|(^\d{5})$');
This does not:
select * from dual where regexp_like('','(^$)|(^\d{5})$');
nor does
select * from dual where regexp_like('','^\d{0,5}$');
nor does
select * from dual where regexp_like('','^(\d{5})*$');