Oracle Database 12c: how to get index of result from search contains search

Question

I have a Java web service that searches the Oracle 12c database. The problem is that I have 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 of the search word or phrase in the results.

Currently, I tried to take each returned search result and go through them by doing a REGEX search in Java to find the start and end indices needed to highlight the search result on the external interface.

This is great for plain text, but if the requirement is that the user can search for any random arrangement of any characters (for example, # <@ (F #> <) $ *> /\./#&!) #} {} ] [s fdf), a Java search should also find the exact string and return the start and stop index.

I avoid any special characters for Oracle searches, but Java searches require an entirely different set of rules for index searches. Trying to make them match was a nightmare, and usually a Java REGEX search does not find the exact same entries that the search in the Oracle database finds. I believe that the approach I take is completely wrong, and there should be an easy way to also get the occurrence indices of the hte search word (or phrase).

TL; DR: When doing a CONTAINS() search, how could I also find search word indexes or phrases in the returned search results using only the Oracle Database query (so I don’t have to worry about losing the search results until REGEX matches)

Answer 1

Perhaps you are looking for something like REGEXP_INSTR () ? It will return zero if there is no match, otherwise it returns the position of the first character (or first character) of the corresponding 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