oracle 匹配一个字符串长度,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;

UPDATE

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

where d.txt LIKE substr('ABCDEFGHIJ', 1, 1) || '%'

group by d.txt

order by 2 desc;

在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