Split the string and find substrings index in oracle

[Happy S Singh]

I have a string like this TESTAPP_B_ABCD_JP_0_FILENAME I want the index of "ABCD" I have tried

select INSTR('TESTAPP_B_ABCD_JP_0_FILENAME', '_ABCD_') from dual;

It returns 10..It means it gives me index counting the characters

I want the output as 3 (Splitting the string TESTAPP(1),B(2),ABCD(3)).

Please guide.

[Jeanne Boyarsky]

You don't want the index then. You want a combination of three things:

1) The substring TESTAPP_B_ABCD
2) The number of split strings or the number of underscores +1

Do you know how to do either or both of these independently? (You should be able to figure out substring)