sql – Oracle查询以查找字符串中出现的所有charcter

我必须在toad中编写一个Oracle查询来查找字符串中所有出现的字符.例如,如果我在字符串SSSRNNSRSSR中搜索R,它应该返回位置4,8和11.

我是Oracle新手并试过这个.

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

其中mtr_ctrl_flags是列名.我收到错误,表明pos是无效的标识符.

解决方法

扩展GolezTrol的答案,您可以使用正则表达式来显着减少您执行的递归查询的数量：

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

REGEXP_COUNT()返回模式匹配的次数,在这种情况下是SS在SSSRNNSRSSR中存在的次数.这会将递归级别限制为您需要的确切数字.

INSTR()只是在你的字符串中搜索R的索引. level是递归的深度,但在这种情况下,它也是字符串出现的级别,因为我们限制了所需的递归次数.

如果您想要选择的字符串更复杂,那么您可以使用正则表达式ans REGEXP_INSTR()而不是INSTR()但它会更慢(不是太多)并且除非需要,否则它是不必要的.

所要求的简单基准：

两个CONNECT BY解决方案将指示使用REGEXP_COUNT对此大小的字符串快20％.

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',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

流水线表函数有点慢,但看看它如何在具有大量匹配的大字符串上执行会很有趣.

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

原文链接：https://www.f2er.com/mssql/76536.html

sql – Oracle查询以查找字符串中出现的所有charcter

解决方法

猜你在找的MsSQL相关文章