Entry

Extracting the longest common preffix of 2 varchars in PL/SQL

Oct 6th, 2005 19:16
Shirley Cohen,

CREATE OR REPLACE FUNCTION COMMON_PREFFIX(par1 VARCHAR2, par2 VARCHAR2)
RETURN VARCHAR2
IS
indx NUMBER;
substring VARCHAR2(4000);
min_len NUMBER;

BEGIN

indx:=1;

if (LENGTH(par1) < LENGTH(par2)) then
min_len:= LENGTH(par1);
else
min_len:= LENGTH(par2);
end if;

FOR indx IN 1..min_len LOOP

if SUBSTR(par1,indx,1) = SUBSTR(par2,indx,1) then
substring:= CONCAT(substring,SUBSTR(par1,indx,1));
else
exit;
end if;

END LOOP;

RETURN(substring);

END;
/