Oracle：获取部分字符串匹配的长度

想象一下,我有一张桌子

Name
----
ABCDEFG
ABChello world
ABCDEfoo
ABbar
ABCDEF
ABCDEFGHIJKLMNOP
zzz
qABCD
ABCqqqGH
ABCABC

我想做一个查询并找出每个字符串中有多少个字符匹配所需的字符串“ABCDEFGHIJ”,始终从头开始.那是…

Name               MatchingLength
----               ----
ABCDEFG            7
ABChello world     3
ABCDEzoo           5
ABbar              2
ABCDEF             6
ABCDEFGHIJKLMNOP   10
zzz                0
qABCD              0
ABCqqqGH           3
ABCABC             3

有没有办法在Oracle中干净利落地做到这一点？我不知所措.

解决方法

-- The hardcoded,bad performance. No transformation of your string though.
with patterns as (
        select substr('ABCDEFGHIJ',1,rownum) txt
        from dual 
        connect by level <= length('ABCDEFGHIJ')
    )
select d.txt,coalesce(max(length(p.txt)),0)
from dummy d
    left join patterns p
        on instr(d.txt,p.txt) = 1
group by d.txt
order by 2 desc;

-- The cool one with regex. 
-- Though transforming your input string,-- this can also be done with ease making something that transorms it for you
-- like in the previous example,more complicated task than the previous,-- as oracle sucks with string manipulation. You can however write it in java.
select d.txt,coalesce(LENGTH(REGEXP_SUBSTR(d.txt,'^A(B(C(D(E(F(G(H(I(J)?)?)?)?)?)?)?)?)')),0)
from dummy d;

http://www.sqlfiddle.com/#!4/85ba6/23

UPDATE

with patterns as (
        select substr('ABCDEFGHIJ',p.txt) = 1
where d.txt LIKE substr('ABCDEFGHIJ',1) || '%'
group by d.txt
order by 2 desc;

更新小提琴：http://www.sqlfiddle.com/#!4/37400/6

在oracle 10g上测试生成的查询计划

SELECT STATEMENT,GOAL = ALL_ROWS       
 SORT ORDER BY                  
  SORT GROUP BY NOSORT          
   NESTED LOOPS OUTER           
    INDEX RANGE SCAN    I <<<< Uses the index.
    VIEW                            
     COUNT                  
      CONNECT BY WITHOUT FILTERING                  
       FAST DUAL

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!