开发者

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

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开发者_StackOverflow中文版 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


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜