Oracle Database 12c: How to also get the result index from a contains search

Question

I have a web service in Java that performs searches on an Oracle 12c Database. The problem is I have a CONTAINS() search:

String query = "select * from sys.my_table WHERE CONTAINS(my_clob_field, '" + searchString + "', 1) > 0";

but I also want to get the index(es) of the search word or phrase within the results.

Currently what I've tried is to take each of the returned search results and go through them performing a REGEX search in Java to find the start and stop indexes needed to highlight the search result on the front end.

This works fine for regular text but if the requirement is that the user can search for any random arrangement of any characters (i.e. #<@(F#><)$*>/\./#&!)#}{}][s fdf) the Java search needs to also find that exact string and return the start and stop index.

I escape out any special characters for the Oracle search, but an entirely different set of rules is needed for the Java search to find the index. Trying to get them to match up has turned out to be a nightmare and typically the Java REGEX search doesn't find the same occurrences that the Oracle Database search finds. I believe the approach I'm taking is totally wrong and there should be some simple way to also get the indexes of the occurrences of hte search word (or phrase).

TL;DR: When performing a CONTAINS() search, how could I also find the indexes of the search word or phrase within the returned search results using only an Oracle Database query (so I don't have to worry about losing search results to a REGEX mismatch)?

Answer 1

Perhaps you are looking for something like REGEXP_INSTR()? It will return zero if no match, otherwise returns the position of the first character of (or the first character following) the matching substring.

select 
  mytab.*,
  regexp_substr(my_clob_field, searchString),
  regexp_instr(my_clob_field, searchString)
from sys.my_table mytab
WHERE CONTAINS(my_clob_field, '" + searchString + "', 1) > 0