regexp_like that mirrors contains near

I’m trying to speed up a query that uses Contains Near with one that uses regexp_like. The initial Contains Near query takes about 45 minutes to run. Clob Column holds large “documents” and is domain indexed.

Initial query:

SELECT column1
FROM TEST
WHERE CONTAINS(column1,'{NEAR(quick,fox, lazy), 3, FALSE}')>0;

​x
 
SELECT column1FROM TESTWHERE CONTAINS(column1,'{NEAR(quick,fox, lazy), 3, FALSE}')>0;​

Proposed query:

SELECT column1
FROM TEST
WHERE REGEXP_LIKE(column1, '(b(quick|fox|lazy)(?:W+w+){1,6}?W(quick|fox|lazy)(?:W+w+){1,}?W(quick|fox|lazy)b)','i')

 
SELECT column1FROM TESTWHERE REGEXP_LIKE(column1, '(b(quick|fox|lazy)(?:W+w+){1,6}?W(quick|fox|lazy)(?:W+w+){1,}?W(quick|fox|lazy)b)','i')​

I got the original regexp syntax from here: https://www.regular-expressions.info/near.html.

Problem: I get the regexp code to work in html https://www.regextester.com, but when I put it in Oracle it doesn’t find anything. What is wrong with my syntax? I can’t figure it out. Does Oracle handle REGEXP differently?

Answer

Alex, you were exactly right. I don’t see how to select your answer as correct though.

My problem was apparently that I was using regexp parameters that Oracle doesn’t recognize. So, whereas it worked on https://www.regextester.com, it failed to work in Oracle because most of what I used isn’t recognized as usable with regexp in Oracle. I really think Oracle should expand their regexp codes it recognized. This was really frustrating.

Advertisement

Answer