regexp_substr strip text between first forward slash and second one

Question

/abc/required_string/2/ should return abc with regexp_substr

Answer 1

SELECT REGEXP_SUBSTR ('/abc/blah/blah/', '/([a-zA-Z0-9]+)/', 1, 1, NULL, 1) first_val 
from dual;

Answer 2

You might try the following:

SELECT TRIM('/' FROM REGEXP_SUBSTR(mycolumn, '^\/([^\/]+)'))
  FROM mytable;

This regular expression will match the first occurrence of a pattern starting with / (I habitually escape /s in regular expressions, hence \/ which won't hurt anything) and including any non-/ characters that follow. If there are no such characters then it will return NULL.

Hope this helps.

Answer 3

You can search for /([^/]+)/, which says:

• / forward slash
• ( start of subexpression (usually called "group" in other languages)
  • [^/] any character other than forward slash
  • + match the preceding expression one or more times
• ) end of subexpression
• / forward slash

You can use the 6th argument to regexp_substr to select a subexpression. Here we pass 1 to match only the characters between the /s:

select  regexp_substr(txt, '/([^/]+)/', 1, 1, null, 1)
from    t1

See it working at SQL Fiddle.

Answer 4

Classic SUBSTR + INSTR offer a simple solution; I know you specified regular expressions, but - consider this too, might work better for a large data volume.

SQL> with test (col) as
  2    (select '/abc/required_string/2/' from dual)
  3  select substr(col, 2, instr(col, '/', 1, 2) - 2) result
  4  from test;

RES
---
abc

SQL>

Answer 5

Here's another way to get the 2nd occurrence of a string of characters followed by a forward slash. It handles the problem if that element happens to be NULL as well. Always expect the unexpected!

Note: If you use the regex form of [^/]+, and that element is NULL it will return "required string" which is NOT what you expect! That form does NOT handle NULL elements. See here for more info: [https://stackoverflow.com/a/31464699/2543416]

with tbl(str) as (
  select '/abc/required_string/2/' from dual union all
  select '//required_string1/3/' from dual
)
select  regexp_substr(str, '(.*?)(/)', 1, 2, null, 1)
from    tbl;