Find substring after nth occurrence of substring in a string in oracle

Question

I want to write an oracle query to find the substring after nth occurrence of a specific substring couldn't find solution for it Example string - ab##cd##gh How to get gh from above string i.e. string after second occurrence of ##

Answer 1

This will return everything after second occurance of ##:

substr(string, instr(string, '##', 1, 2)+1) 

If you need to find a substring with specific length, then just add third parameter to substr function

substr(string, instr(string, '##', 1, 2)+1, 2) 

You can also use it in query:

select 
  substr(some_value, instr(some_value, '##', 1, 2)+1, 2) 
from some_table
where...