How to get string in between forth and fifth slash(/) in oracle without using regular expressions

Question

I have a string like below

/doc/Alldocs/attachment/2345455/122222/0/C/0%20%XYZ%ABC%20K

I need to get the string in between the forth and fifth slash that means 2345455

Currently I am using REGEXP_SUBSTR to get the result.

REGEXP_SUBSTR('/doc/Alldocs/attachment/2345455/122222/0/C/0%20%XYZ%ABC%20K', '[^/ ]+', 1, 4)

But this is really impacting the performance, in my database it is taking long time to return the result.

Is there any other way we can get this information faster ? something like split, Instr etc ??

I am newbie to oracle could you please help me to resolve this ?

Answer 1

Using the old SUBSTR + INSTR combination, as usual.

SQL> with test (col) as
  2    (select '/doc/Alldocs/attachment/2345455/122222/0/C/0%20%XYZ%ABC%20K' from dual)
  3  select substr(col, instr(col, '/', 1, 4) + 1,
  4                     instr(col, '/', 1, 5) - instr(col, '/', 1, 4) - 1
  5               ) result
  6  from test;

RESULT
-------
2345455

SQL>

• the first INSTR searches for the 4th slash
• the second INSTR searches 5th slash position and subtracts position of the 4th slash - the result is the length of the string to be retrieved.