CODEWITHSUNDEEP
sqloracle-databasehibernatejpa
Gurru
Gurru

Reputation: 2103

JPA oracle substring

how to write a named query for Oracle using Hibernate SELECT * FROM Table where substring(Car, 2, 1) = '1' oracle has substr intead of substring and i know that JPA has substring as well, is it the same function?

Upvotes: 0

Views: 589

Answers (1)

Seyed Mostafa Mousavi Kahaki
Seyed Mostafa Mousavi Kahaki

Reputation: 317

Create the procedure as:

CREATE OR REPLACE FUNCTION GET_SUB_STRING(STR IN VARCHAR2, STR_DELIMITOR IN VARCHAR2:='.',STR_PART IN VARCHAR2:='1')RETURN VARCHAR2 IS STR_RET VARCHAR2(4000):=NULL; BEGIN IF STR_PART = '1' THEN
STR_RET := substr(STR, 1, instr(STR,STR_DELIMITOR,1,1)-1);
ELSE
STR_RET := substr(STR, instr(STR,STR_DELIMITOR,1,STR_PART-1)+1,instr(STR, STR_DELIMITOR, 1,STR_PART)- instr(STR, STR_DELIMITOR, 1,STR_PART-1)-1);
END IF;
RETURN STR_RET; END;

Then you can use it like this:

SELECT GET_SUB_STRING(COLUMN_NAME,',','1') FROM YOUR_TABLE

Upvotes: 1

Related Questions