CODEWITHSUNDEEP
oracle-databaseoracle11gplsqldeveloper
Arunprasanth K V
Arunprasanth K V

Reputation: 21931

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

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 ?

Upvotes: 0

Views: 679

Answers (2)

Littlefoot
Littlefoot

Reputation: 142788

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.

Upvotes: 1

hotfix
hotfix

Reputation: 3396

here is a quick solution with substr and ìnstr

  • first you can get a string /doc/Alldocs/attachment/2345455

    substr(str, 1, length(substr(str,1,instr(str,'/',1,5)))-1)

  • than you can first part of a string

    with tab as(
  select '/doc/Alldocs/attachment/2345455/122222/0/C/0%20%XYZ%ABC%20K' as str from dual
)
select  substr(
           substr(str, 1, length(substr(str,1,instr(str,'/',1,5)))-1)
       ,instr(str,'/',1,4)+1)
  from tab

Upvotes: 1

Related Questions