PL/SQL Oracle regular expression doesn't work for occurencce of zero

Question

Hi everyone,

I have a problem in matching regular expression in Oracle PL/SQL. To be more specific, the problem is that regex doesn't want to match any zero occurrence. For example, I have something like:

select * from dual where regexp_like('', '[[:alpha:]]*');

and this doesn't work. But if I put space in this statement:

select * from dual where regexp_like(' ', '[[:alpha:]]*');

it works.

I want to have the first example running, so that person doesn't have to put 'space' for it to work. Any help is appreciated, and thank you for your time.

T

Answer 1

Does Oracle still treat empty strings as NULLs? And if so, does regexp_like with a NULL input source string return UNKNOWN? Both of these would be semi-reasonable, and a reason why your test does not work as you expected.

Answer 2

Hi,

For better or worse, empty strings in Oracle are treated as NULL:

SQL> select * from dual where '' like '%';

DUMMY
-----

Take that into account when querying with Oracle:

SQL> SELECT *
  2    FROM dual
  3   WHERE regexp_like('', '[[:alpha:]]*')
  4      OR '' IS NULL;

DUMMY
-----
X