Method to find multiple occurrences of a one or more strings within a varchar2. Sort of an instr on steroids...
In the example above, I used my Mark Knopfler lyrics table. The exact setup of that table is not really important here, all you need is a table with a varchar2 column (or a clob, as long as it's not bigger than 4000 bytes).
Support for (bigger) clobs would be nice to have, but since this solution uses distinct and the "lower" function to make the search case-insensitive, that would be a bit of a challenge.
You may want to change the "connect by level <= 100" to some other value, if you expect to find the search pattern more than 100 times per varchar2. Note that this value does not have a big impact on performance, so I used 100 here to be on the safe side.
Searching the internet for other solutions did not produce many results. And the ones I did find used regular expression functions like regexp_count, and were very slow.
A (slow) example of that approach could look something like this:
An interesting link:
http://stackoverflow.com/questions/17906105/oracle-query-to-find-all-occurences-of-a-charcter-in-a-string.