how to split the value in oracle

Question

I have worked with MySQL and new to oracle. In MySQL we have a function SUBSTRING_INDEX(), I want to replace it in oracle, Any help plz

 SQL>select SUBSTRING_INDEX('JD;EQ;0001', ';', -1) from dual;

Result:

0001

I want a same result in oracle. Is there any function in oracle that return the same result in oracle?

I have tried but no expected result.

  SELECT substr('CLUBORACLE',3,2) RES FROM dual;

I think you'll find this to be a useful resource: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm — David Aldridge, Jul 22 '16 at 10:21

score 2 · Answer 1 · edited May 23 '17 at 11:58

2

SELECT SUBSTR('JD;EQ;0001', INSTR('JD;EQ;0001', ';', -1) + 1) FROM dual

In the query above, INSTR('JD;EQ;0001', ';', -1) would return 6, which is the position of the last semicolon in the expression. You want to take the substring from the position after the last semicolon until the end of the string.

Look here for a good SO question about Oracle's INSTR.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 22 '16 at 09:24

Tim Biegeleisen

502,043
27
286
360

nilsman · Answer 2 · 2016-07-22T10:15:41.670

0

You can also use regex: SELECT REGEXP_SUBSTR('JD;EQ;0001', '[^;]+', 1, your_occurance_number) FROM dual; but SUBSTR+INSTR should be faster.

edited Jul 22 '16 at 10:15

answered Jul 22 '16 at 09:44

nilsman

346
1
9

how to split the value in oracle

2 Answers2