4

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 ##

Pranjali
  • 43
  • 1
  • 1
  • 6
  • 1
    Classical question, please tell us what have you tried so far? – Barbaros Özhan Mar 15 '18 at 17:31
  • Can there ever be more than two consecutive `#`? If so, how should that be handled? For example: `ab###cde##fg` - the first occurrence is immediately after `ab`. Is the **second** occurrence the second and third `#` between `ab` and `cde`? Or is it the one between `cde` and `fg`? Also: in `abcd####xyz`, is the first occurrence the first two `#`, the second occurrence the third and fourth `#`, and the substring between them is the empty string (same as `null` in Oracle)? Did you even consider these questions? They must be answered before you can write any kind of code. –  Mar 15 '18 at 17:44
  • Hi, welcome to SO. If you have written code for this that you can't get to work, then you have come to the right place. Just edit your question and add the relevant parts of your code into it. You need to show your own efforts, because Stack Overflow is not a write-my-code-for-me service. Please see [How to Ask](https://stackoverflow.com/questions/how-to-ask). – Dave Mar 15 '18 at 17:49
  • Possible duplicate of [REGEX to select nth value from a list, allowing for nulls](https://stackoverflow.com/questions/25648653/regex-to-select-nth-value-from-a-list-allowing-for-nulls) – Patrick Bacon Mar 15 '18 at 17:51

2 Answers2

8

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...
Jakubeeee
  • 666
  • 6
  • 13
-1
 SELECT 'ab##cd##gh' String,
 Substr('ab##cd##gh',Instr('ab##cd##gh','gh',-1,1),2) Substr
 FROM Dual;

STRING          SU
----------      --
ab##cd##gh      gh
Abra
  • 19,142
  • 7
  • 29
  • 41
chikkili j
  • 1
  • This already has a formatted and accepted answer. Can you format yours and provide why it is better/what it adds than the accepted answer? – gilliduck Mar 22 '20 at 16:50
  • 1
    I have formatted your answer but you should still try to explain how it works. – Abra Mar 22 '20 at 18:54