Oracle Regexp_substr String

Question

I have String like a123bcd-e2343fg-hij-dfgh and I want OUTPUT e2343fg-hij-dfgh using Regular_expression in oracle.

select regexp_substr('abcd-efg-hij','-[^-]+'1) from dual;

Answer 1

select substr('abcd-efg-hij',
               regexp_instr('abcd-efg-hij','-[^-]+')+1,length('abcd-efg-hij')) 
  from dual;

try this

Answer 2

You may apply regexp_substr with [^-]+[-^] pattern and then ltrim as :

select ltrim('a123bcd-e2343fg-hij-dfgh',
         regexp_substr('a123bcd-e2343fg-hij-dfgh','[^-]+[-^]')) as output_string        
  from dual;

or better to call with bind variable :

select ltrim('&str', regexp_substr('&str','[^-]+[-^]')) as output_string
  from dual;

where &str may be replaced with a123bcd-e2343fg-hij-dfgh after prompted.

Rextester Demo

Answer 3

Why regular expression, when a trivial SUBSTR + INSTR does the job nicely & quickly? True, it will look smarter, but I can't see any other benefit.

SQL> with test (col) as
  2    (select 'a123bcd-e2343fg-hij-dfgh' from dual)
  3  select substr(col, instr(col, '-') + 1) result
  4  from test;

RESULT
----------------
e2343fg-hij-dfgh

SQL>

Answer 4

For the sake of argument, regexp_replace works too. This regex matches anything up to and including the first dash, and remembers the rest which it returns.

with tbl(str) as (
  select 'a123bcd-e2343fg-hij-dfgh' from dual
)
select regexp_replace(str, '^.*?-(.*)', '\1')
from tbl;

Keep in mind if regexp_substr() does not find a match, it returns NULL but if regexp_replace() does not find a match it return the original string.