Jaro-Winkler Matching Algorithm

CREATE OR REPLACE FUNCTION jaro(str1_in IN VARCHAR2, str2_in IN VARCHAR2) RETURN NUMBER AS
    /* Converted from VB code from:
        forums.devshed.com/software-design-43/jaro-winkler-matching-algorithm-62100 DOT html
        
        Oracle implementation by Stew Stryker (stewDOTstrykerATdartmouthDOTedu) 4/30/07
    */

    str1 VARCHAR2(4000);
    str2 VARCHAR2(4000);
    len_str1 PLS_INTEGER;
    len_str2 PLS_INTEGER;
    swap_len PLS_INTEGER;
    max_len PLS_INTEGER;
    m PLS_INTEGER;
    i PLS_INTEGER;
    j PLS_INTEGER;
    f PLS_INTEGER;
    l PLS_INTEGER;
    tr NUMBER := 0;
    a1 VARCHAR2(4000);
    a2 VARCHAR2(4000);
    swap_str VARCHAR2(4000);

    TYPE bool_list_t IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
    f1 bool_list_t;
    f2 bool_list_t;

    wcd NUMBER;
    wrd NUMBER;
    wtr NUMBER;
    common NUMBER := 0;
    jaro_value NUMBER := 0;

    -- Local function
    -- clean_string normalizes the data as of COMPANY -> CO or CORPORATION -> CORP. Example:
    FUNCTION clean_string(p_str1 IN VARCHAR2) RETURN VARCHAR2 IS
        ret_value VARCHAR2(4000) := p_str1;
    BEGIN
        ret_value := REPLACE(ret_value, '.', '');
        ret_value := REPLACE(ret_value, ',', '');
        ret_value := REPLACE(ret_value, '-', '');
        ret_value := REPLACE(ret_value, ';', '');
        ret_value := REPLACE(ret_value, ':', '');
        ret_value := REPLACE(ret_value, 'Á', 'A');
        ret_value := REPLACE(ret_value, 'É', 'E');
        ret_value := REPLACE(ret_value, 'Í', 'I');
        ret_value := REPLACE(ret_value, 'Ó', 'O');
        ret_value := REPLACE(ret_value, 'Ú', 'U');
        ret_value := REPLACE(ret_value, ' COMPANY ', ' CO ');
        ret_value := REPLACE(ret_value, ' CORPORATION ', ' CO ');
        RETURN ret_value;
    END clean_string;

    -- MAIN
BEGIN
    str1 := clean_string(str1_in);
    str2 := clean_string(str2_in);

    len_str1 := LENGTH(str1);
    len_str2 := LENGTH(str2);

    IF len_str1 > len_str2
    THEN
        swap_len := len_str2;
        len_str2 := len_str1;
        len_str1 := swap_len;
        swap_str := str1;
        str1 := str2;
        str2 := swap_str;
    END IF;

    max_len := len_str2;

    FOR i IN 1 .. len_str1
    LOOP
        f1(i) := FALSE;
    END LOOP;

    FOR j IN 1 .. len_str2
    LOOP
        f2(j) := FALSE;
    END LOOP;

    m := ROUND((max_len / 2) - 1);

    FOR i IN 1 .. len_str1
    LOOP
        a1 := SUBSTR(str1, i, 1);
    
        IF m >= i
        THEN
            f := 1;
            l := i + m;
        ELSE
            f := i - m;
            l := i + m;
        END IF;
    
        IF l > max_len
        THEN
            l := max_len;
        END IF;
    
        FOR j IN f .. l
        LOOP
            a2 := SUBSTR(str2, j, 1);
            IF (a2 = a1)
               AND (f2(j) = FALSE)
            THEN
                common := common + 1;
                f1(i) := TRUE;
                f2(j) := TRUE;
                GOTO linea_exit;
            END IF;
        END LOOP; -- j
    
        <<linea_exit>>
        NULL; -- Add NULL statement to avoid error
    END LOOP; -- i

    l := 1;
    FOR i IN 1 .. len_str1
    LOOP
    
        IF f1(i)
        THEN
            FOR j IN l .. len_str2
            LOOP
                IF f2(j)
                THEN
                    l := j + 1;
                    a1 := SUBSTR(str1, i, 1);
                    a2 := SUBSTR(str2, j, 1);
                    IF a1 <> a2
                    THEN
                        tr := tr + 0.5;
                    END IF;
                    GOTO j_loop;
                END IF;
            END LOOP; -- j
            <<j_loop>>
            NULL; -- Add NULL statement to avoid error        
        END IF;
    END LOOP; -- i

    wcd := 1 / 3;
    wrd := 1 / 3;
    wtr := 1 / 3;

    IF common <> 0
    THEN
        jaro_value := wcd * common / len_str1 + wrd * common / len_str2 +
                      wtr * (common - tr) / common;
    END IF;

    RETURN jaro_value;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('EXCEPTION IN jaro - ' || SQLCODE || ': ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Error stack at top level:');
        DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
        RETURN jaro_value;
END jaro;