想象一下,我有一张桌子
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
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
