Geek Answers Handbook

Oracle: get the length of a partial string match

Imagine I have a table like

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

I want to make a request and find out how many characters of each line correspond to the search string "ABCDEFGHIJ", always starting from the very beginning. It...

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

Is there any way to do this in Oracle? I'm at a loss.

+4
source share
6 answers

I do not know about the "clean", but there are two solutions.

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

Updated script: http://www.sqlfiddle.com/#!4/37400/6

Request plan prepared on 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
+4
source

Assuming you only need to have a matching counter for those lines that start with ABCDEFGHIJ , and for lines of type qABCD match will be 0

  SELECT STR,DECODE(SUBSTR(STR,1,1),'A',LENGTH(STR)- NVL(LENGTH(REPLACE(TRANSLATE(STR,'ABCDEFGHIJ',' '),' ','')),0),0) MATCHING_LENGTH FROM table
0
source

If you are using Oracle 11gR2, you can use Recursive common table expressions as follows:

 with rcte(txt,t, p, c) as ( select d.txt , d.txt t, 'ABCDEFGHIJ' p, 0 c from dummy d union all select txt ,substr(t, 2), substr(p, 2), case when substr(t, 1, 1) = substr(p, 1, 1) then 1 else 0 end from rcte where length(t) > 0 and length(p) > 0 and substr(t, 1, 1) = substr(p, 1, 1) ) select txt, sum(c) from rcte group by txt;

Here is the sqlfiddle daemon (thanks @Roger)

0
source

Assuming that the matching criteria is equal to both the character and the position (the value of the ABCqqqGH pattern is 5), you can try the following:

 17:57:03 SYSTEM@sandbox > @sf test VAL ------------------------------ ABCDEFG ABChello world ABCDEzoo ABbar ABCDEF ABCDEFGHIJKLMNOP zzz qABCD 8 rows selected. Elapsed: 00:00:00.01 17:57:05 SYSTEM@sandbox > @get match 1 select t.val, count(l) 2 from test t 3 left join (select level l from dual connect by level <= length('ABCDEFGHIJ')) i 4 on substr(t.val, il, 1) = substr('ABCDEFGHIJ', il, 1) 5 group by t.val 6* order by 2 desc 17:57:07 SYSTEM@sandbox > / VAL COUNT(L) ------------------------------ ---------- ABCDEFGHIJKLMNOP 10 ABCDEFG 7 ABCDEF 6 ABCDEzoo 5 ABChello world 3 ABbar 2 zzz 0 qABCD 0 8 rows selected. Elapsed: 00:00:00.02
0
source 
 declare v_1 number := 0; v_pattern VARCHAR(26) := '&n'; v_f number; v_spaces VARCHAR(30) := ' '; v_l number; v_c varchar(20) := ' '; v_n varchar(20) := ' '; BEGIN v_f := Ascii(SubStr(v_pattern,1,1)); v_l := Ascii(SubStr(v_pattern,Length(v_pattern))); v_spaces := LPad(' ',Length(v_pattern),' '); for i in (select str,TRANSLATE(REPLACE(str,' ',''),v_pattern,v_spaces) c1,length(REPLACE(str,' ',''))-nvl(length(replace(TRANSLATE(REPLACE(str,' ',''),v_pattern,v_spaces),' ','')),0) c2 from table where ascii(substr(str,1,1)) IN (SELECT DISTINCT Ascii(SubStr(v_pattern,LEVEL,1)) FROM dual CONNECT BY LEVEL<=Length(v_pattern))) loop for j in 1..i.c2 loop v_c :=instr(i.c1,' ',1,j); v_n :=instr(i.c1,' ',1,j+1); if v_c+1=v_n then v_1 := v_1+1; end if; end loop; if(v_1+1 = i.c2) then dbms_output.put_line('String : '||i.str||' and Matching count : '||i.c2); else dbms_output.put_line('String : '||i.str||' and Matching count : '||((v_1)-1)); end if; v_1 := 0; end loop; FOR k IN (SELECT str FROM table WHERE NOT(Ascii(substr(str,1,1)) IN (SELECT DISTINCT Ascii(SubStr(v_pattern,LEVEL,1)) FROM dual CONNECT BY LEVEL<=Length(v_pattern)))) LOOP dbms_output.put_line('String : '||k.str||' and Matching count : '||v_1); END LOOP; end;
0
source

Hope the following helps:

 CREATE TABLE TESTME ( TNAME VARCHAR2(30)); INSERT INTO TESTME VALUES('ABCDEFG'); INSERT INTO TESTME VALUES('ABChello world'); INSERT INTO TESTME VALUES('ABCDEzoo'); INSERT INTO TESTME VALUES('ABbar'); INSERT INTO TESTME VALUES('ABCDEF'); INSERT INTO TESTME VALUES('ABCDEFGHIJKLMNOP'); INSERT INTO TESTME VALUES('zzz'); INSERT INTO TESTME VALUES('qABCD'); CREATE OR REPLACE FUNCTION GET_MLENGTH( P_INPUT VARCHAR2) RETURN NUMBER IS -- COMBARING STRING C VARCHAR2(10) := ('ABCDEFGHIJ'); N NUMBER := 0; BEGIN FOR I IN 1..LENGTH(P_INPUT) LOOP IF SUBSTR(P_INPUT,I,1) = SUBSTR(C,I,1) THEN N := N + 1; ELSE RETURN N; END IF; END LOOP; RETURN N; END; / SELECT TNAME , GET_MLENGTH(TNAME) FROM TESTME ; TNAME GET_MLENGTH(TNAME) ------------------------------ ------------------ ABCDEFG 7 ABChello world 3 ABCDEzoo 5 ABbar 2 ABCDEF 6 ABCDEFGHIJKLMNOP 10 zzz 0 qABCD 0
-1
source

More articles:


All Articles