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

Question

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

I am new to Oracle and have 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 name of the column. I am getting an error indicating that pos is an invalid identifier.

Answer 1

GolezTrol's response extension allows the use of regular expressions to significantly reduce the number of recursive queries:

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

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

INSTR () just looks for the index R in your string. level is the depth of the recursion, but in this case it is also the th level of the string entry, since we limited the number of recursions required.

If the line you want to select is harder, you can use the regular expressions ans REGEXP_INSTR () as opposed to INSTR (), but it will be slower (not much), and this is optional unless required.

---

Simple criteria on request:

Two CONNECT BY solutions indicate that using REGEXP_COUNT is 20% faster on a row 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 pipeline table function works more slowly, although it would be interesting to see how it runs on large rows with a lot 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 

Answer 2

This solution:

 select pos from (select substr('SSSRNNSRSSR', level, 1) as character, level as pos from dual connect by level <= length(t.text)) where character = 'R' 

dual is an inline table that returns only one row. Very comfortably!

connect by allows you to create recursive queries. This is often used to generate lists from tree data (parent / child relationship). This allows you to more or less repeat the request before it. And you have special fields, such as level , that let you check how deep the recursion has gone.

In this case, I use it to split the string into characters and return a string for each character. Using level , I can repeat the query and get the character until the end of the line is reached.

Then it's just a matter of returning pos for all lines containing the character 'R'

Answer 3

To make a_horse_with_no_name call , here is another answer with a pipelined table function .

The pipeline function returns an array that you can query normally. I would expect this to work better on rows with more matches than a recursive query, but, like everyone else, check yourself first.

 create type num_array as table of number / create function string_indexes ( PSource_String in varchar2 , PSearch_String in varchar2 ) return num_array pipelined is begin for i in 1 .. length(PSource_String) loop if substr(PSource_String, i, 1) = PSearch_String then pipe row(i); end if; end loop; return; end; / 

Then to access it:

 select * from table(string_indexes('SSSRNNSRSSR','R')) 

SQL Fiddle