Skip to Main Content

Settings

Select a value for 1 or 2 Search Patterns and click Search to refresh the report.

IR

No data found.

Info

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.

Code

Item

Identification
Sequence
Name
Display As
User Interface
Region
List Of Values
Display Extra Values
Display Null Value
List of values definition
Advanced
Warn on Unsaved Changes
Source
Source Used
Source Type

Item

Identification
Sequence
Name
Display As
User Interface
Region
List Of Values
Display Extra Values
Display Null Value
List of values definition
Advanced
Warn on Unsaved Changes
Source
Source Used
Source Type

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source