JPA: Equivalent for Oracle's REGEXP_SUBSTR

Question

I have following Oracle SQL query:

SELECT SUBSTR(col, 0, INSTR(col, REGEXP_SUBSTR(col, '\.\d+$')) -1) AS col_new, col as col_orig AS col_orig FROM tab;

I have data in table like:

col
ABC.A.01
ABC.A.02

Above query returns results like:

col_new  col_orig
ABC.A    ABC.A.01
ABC.A    ABC.A.02

I am trying to migrate it to JPA named query. Till now I could make query only like this:

SELECT SUBSTRING(f.col, 0, LENGTH(f.col) - LOCATE('.', REVERSE(f.col))), f.col FROM tab f;

I did this as I was not able to find equivalent in JPA for Oracle's REGEXP_SUBSTR. My JPA named query fails in data examples like ABC.A.P01.

Can you please let me know how can I migrate my SQL query to JPA named query using equivalent for REGEXP_SUBSTR.

Answer 1

I found that there is no equivalent for REGEXP_SUBSTR in JPA. So I decided to stick to native query execution.