Oracle: REGEXP_LIKE: Strings with angle brackets aren’t found (whether they are escaped or not)

javaoracleregex

The following question will answer my original question, plus many others. What would the correct syntax be to do a REGEXP_LIKE search (in Oracle Database 12c) for this string (which exists somewhere in a CLOB)? In other words, pretend like you want to find this exact string:

%.,"'?!:#$&)(*;+-/<>=@[]\^_{}|~

using: 

"select * from my_table where regexp_like (some_column, '" + regexSearchString + "')"

Now, from everything I've read, you would need to transform the string by escaping the necessary characters (\ ^ . $ | ( ) [ ] * + ? { } ,) to get it to work, like this:

%\.\,"'\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~

Now I haven't tested every non-escaped character like &, _, or %, but I have found that searches with < or > are not found. Even though REGEXP_LIKE is not needed in the following example, say I wanted to find all occurrences of ><div class=, for whatever reason. Is there an additional thing that needs to be done to get it to correctly treat angle brackets just like any other letter?

Best Answer

select 1 from dual where
regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~', 
            '%\.,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~');

This gives, as expeced, 1.

You must escape .?$)(*+[]\^{} and |

You do not need to escape , (but it works nonetheless):

select 1 from dual where
regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~', 
            '%\.\,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~');

This works:

select 1 from dual where
regexp_like('<div>Lets check for an <strong>inner</strong> tag :)</div>', 
            '<(\w+)>[^<]*</\1>');

Gives 1. So does what you ask for:

select 1 from dual where
regexp_like('<p>Lets get <i>this</i><div class="example">example going :)</div></p>', 
            '><div class=');

For which you don't even need regex, by the way.

EDIT: By guess is, that you are using a programming language to build your SQL. In that case, it is highly likely that you are not properly escaping your . If you want the SQL to include '\.' (an escaped .), you need to escape it for your lanuage too: '\\.' Likewise, if you want '\\\^' you'd need to write '\\\\\\^'

Related Question