问题描述
我必须在蟾蜍中编写一个Oracle查询,以查找字符串中所有出现的字符.例如,如果我在字符串SSSRNNSRSSR
中搜索R
,它应该返回位置4、8和11.
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.
我是Oracle新手,并且尝试过此操作.
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
);
其中mtr_ctrl_flags
是列名.我收到一条错误消息,指出pos
是无效的标识符.
where mtr_ctrl_flags
is the column name. I'm getting an error indicating that pos
is an invalid identifier.
推荐答案
扩展GolezTrol的答案,您可以使用正则表达式来显着减少您执行的递归查询的数量:
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()返回模式匹配的次数,在这种情况下,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()仅在以下位置搜索R的索引您的字符串. level
是递归的深度,但是在这种情况下,它也是字符串的第 个级别,因为我们限制了所需的递归次数.
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.
如果您要选择的字符串比较复杂,则可以使用正则表达式ans ,而不是INSTR(),但速度较慢(不是很多),除非有必要,否则没有必要.
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:
这两个CONNECT BY解决方案表明,使用REGEXP_COUNT,在这种大小的字符串上使用速度快20%.
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
这篇关于Oracle查询以查找字符串中所有出现的字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!