shelley shelley - 1 year ago 114
SQL Question

Oracle Regular Expression for Null Character

In Oracle 11g, how can I detect a null character (

) in a regular expression?

I would expect the following condition to evaluate to true, but it does not:

select case when regexp_like (chr(0), '\0') then 1 else 0 end from dual;

My specific use case requires detecting a pattern of null characters within a varchar2 column:

select * from my_table where NOT regexp_like (my_column, '^[\s\0]+$');

Answer Source

It does not seem to be possible to identify just the null character within an Oracle regular expression. Oracle's Regular Expression reference documentation indicates what regular expressions are supported, and makes no mention of supporting a metacharacter or expression that would identify a null character (although a subset of other standard metacharacters are supported). My tests have confirmed this, as have some follow-up tests executed by others in this thread.

Although the answer to this question is that it's not possible, some of the other answers may help satisfy others' use cases. In particular, please see this answer suggesting the use of the [[:cntrl:]] class. This will identify null characters in a regular expression, although it will also match on other characters in the class.