Oracle query to find all occurrences of a charcter in a string

Question

I have to write an Oracle query in toad to find all the occurrences of a character in a string. For example if I'm searching for R in the string SSSRNNSRSSR, it should return positions 4, 8 and 11.

I am new to Oracle and tried this.

select instr(mtr_ctrl_flags, 'R', pos + 1, 1) as pos1 
  from mer_trans_reject  
 where pos in ( select instr(mtr_ctrl_flags, 'R', 1, 1) as pos 
                  from mer_trans_reject
                       );

where mtr_ctrl_flags is the column name. I'm getting an error indicating that pos is an invalid identifier.

Answer 1

Extending GolezTrol's answer you can use regular expressions to significantly reduce the number of recursive queries you do:

 select instr('SSSRNNSRSSR','R', 1, level)
   from dual
connect by level <= regexp_count('SSSRNNSRSSR', 'R')

REGEXP_COUNT() returns the number of times the pattern matches, in this case the number of times R exists in SSSRNNSRSSR. This limits the level of recursion to the exact number you need to.

INSTR() simply searches for the index of R in your string. level is the depth of the recursion but in this case it's also the level th occurrence of the string as we restricted to the number of recurses required.

If the string you're wanting to pick out is more complicated you could go for regular expressions ans REGEXP_INSTR() as opposed to INSTR() but it will be slower (not by much) and it's unnecessary unless required.

---

Simple benchmark as requested:

The two CONNECT BY solutions would indicate that using REGEXP_COUNT is 20% quicker on a string of this size.

SQL> set timing on
SQL>
SQL> -- CONNECT BY with REGEX
SQL> declare
  2     type t__num is table of number index by binary_integer;
  3     t_num t__num;
  4  begin
  5    for i in 1 .. 100000 loop
  6       select instr('SSSRNNSRSSR','R', 1, level)
  7         bulk collect into t_num
  8         from dual
  9      connect by level <= regexp_count('SSSRNNSRSSR', 'R')
 10              ;
 11     end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.94
SQL>
SQL> -- CONNECT BY with filter
SQL> declare
  2     type t__num is table of number index by binary_integer;
  3     t_num t__num;
  4  begin
  5    for i in 1 .. 100000 loop
  6       select pos
  7         bulk collect into t_num
  8         from ( select substr('SSSRNNSRSSR', level, 1) as character
  9                     , level as pos
 10                  from dual t
 11               connect by level <= length('SSSRNNSRSSR') )
 12        where character = 'R'
 13              ;
 14     end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.80

The pipelined table function is a fair bit slower, though it would be interesting to see how it performs over large strings with lots of matches.

SQL> -- PIPELINED TABLE FUNCTION
SQL> declare
  2     type t__num is table of number index by binary_integer;
  3     t_num t__num;
  4  begin
  5    for i in 1 .. 100000 loop
  6       select *
  7         bulk collect into t_num
  8         from table(string_indexes('SSSRNNSRSSR','R'))
  9              ;
 10     end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.54