2

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?

Improve this question
2

1 Answer 1

Reset to default
0 
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 '\\\\\\^'

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.