Regular Expression to find Regular Expression

For a function in the Oracle database application I work on, we had to identify rows where a certain column contained an Oracle regular expression. Oracle supports regular expressions since version 10. There is the REGEXP_LIKE condition, but also the REGEXP_* functions like REGEXP_SUBSTR.

For our problem we had to dig a little deeper into the documentation, because we would have to escape the regular expression characters in order to find if the haystack contained a regular expression.

I came up with the next code, which seems to serve very well.

select haystack
    ,sign(regexp_instr(haystack,'[]\$^*+?{}.,()|:=[-]',1,1,0,'i')) as is_regexp_yn
from testdata;

Strange thing here is we did not have to escape the special characters! The Oracle regular expression engine seems to understand what we want. Special though in this regular expression “[]\$^*+?{}.,()|:=[-]” are te location of the square close bracket “]” and the hyphen “-“. Better not fiddle too much with it the sequence or you’ll break it…

Comments are closed.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy