Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   nysiis.pls
rem Purpose:        NYSIIS function (an improvement on soundex)
rem Notes:      Convert a name to a phonetic coding of up to six characters
rem Date:       19-Jul-2004
rem Author:     Trevor Fairhurst, trevgf@yahoo.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION NYSIIS (v_text IN VARCHAR2) RETURN VARCHAR2 is

    v_sub      varchar2(300);
    v_length   number(10);
    v_textin   varchar2(300);
    v_key      varchar2(1);

   begin
    SELECT UPPER ( v_text ) into v_textin from dual;
    dbms_output.put_line( 'Entered surname :' || v_textin);

    dbms_output.put_line( ' [1] remove all S and Z chars from the end of the surname ' );
    LOOP
      SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
      dbms_output.put_line('Last letter :' || v_sub);
      if v_sub = 'S' OR v_sub = 'Z' THEN
        SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
        dbms_output.put_line('As last letter s or z drop last letter giving :' || v_textin || ' and check new last letter');
      else
        dbms_output.put_line('Last letter not s or z completed step 1');
        EXIT;
      end if;
    END LOOP;
    dbms_output.put_line('Step 1 completed giving :' || v_textin );

    dbms_output.put_line( ' [2] transcode initial strings MAC => MC and PF => F and PH => F ' );
    if SUBSTR ( v_textin , 1 , 3 ) = 'MAC' THEN
      SELECT 'MC' || SUBSTR ( v_textin , 4 ) into v_textin from dual ; 
    elsif SUBSTR ( v_textin , 1 , 2 ) = 'PH' THEN
      SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
    elsif SUBSTR ( v_textin , 1 , 2 ) = 'PF' THEN
      SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
    end if;

    dbms_output.put_line('Step 2 completed giving :' || v_textin );

    dbms_output.put_line( ' [3] transcode trailing strings IX => IC and EX => EC and YE,EE,IE => Y and NT,ND => D ' );
    SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
    dbms_output.put_line('Last 2 letters :' || v_sub);
    if UPPER(v_sub) in ('IX','EX','YE','EE','IE','NT','ND') THEN
      SELECT decode ( UPPER(v_sub) , 'IX','IC',
                              'EX','EC',
                              'YE','Y',
                              'EE','Y',
                              'IE','Y',
                              'NT','D',
                              'ND','D', NULL  ) into v_sub from dual ;
      SELECT SUBSTR ( v_textin , 1, (length (v_textin) - 1)) || v_sub into v_textin from dual ;
    end if;
    dbms_output.put_line('Step 3 completed giving :' || v_textin );

    dbms_output.put_line( '[4] transcode EV to EF if not at start of name');
    SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'EV','EF') into v_textin from dual ;
    dbms_output.put_line('Step 4 completed giving :' || v_textin );

    dbms_output.put_line( '[5] first character of name as first character of key continue with remaining characters');
    SELECT SUBSTR ( v_textin , 1 , 1) into v_key from dual;
    SELECT SUBSTR ( v_textin , 2 ) into v_textin from dual;
    dbms_output.put_line('Step 5 completed first character of key :' || v_key );

    dbms_output.put_line( '[6] replace all vowels with A');
    SELECT TRANSLATE( v_textin,'AEIOU','AAAAA') into v_textin from dual ;
    dbms_output.put_line('Step 6 completed giving :' || v_textin );

    dbms_output.put_line( '[7] remove any W that follows a vowel');
    SELECT REPLACE( v_textin,'AW','A') into v_textin from dual ;
    dbms_output.put_line('Step 7 completed giving :' || v_textin );

    dbms_output.put_line( '[8] transcode GHT to GT ');
    SELECT REPLACE( v_textin,'GHT','GT') into v_textin from dual ;
    dbms_output.put_line('Step 8 completed giving :' || v_textin );

    dbms_output.put_line( '[9] transcode DG to G   ');
    SELECT REPLACE( v_textin,'DG','G') into v_textin from dual ;
    dbms_output.put_line('Step 9 completed giving :' || v_textin );

    dbms_output.put_line( '[10] transcode PH to F   ');
    SELECT REPLACE( v_textin,'PH','F') into v_textin from dual ;
    dbms_output.put_line('Step 10 completed giving :' || v_textin );

    dbms_output.put_line( '[11] if not first character, eliminate all H preceded or followed by a vowel ');
    if length(v_textin) > 1 THEN
      SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'HA','A') into v_textin from dual ;
      SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'AH','A') into v_textin from dual ;
    end if;
    dbms_output.put_line('Step 11 completed giving :' || v_textin );

    dbms_output.put_line( '[12] change KN to N, else K to C  ');
    SELECT REPLACE( v_textin,'KN','N') into v_textin from dual ;
    SELECT TRANSLATE( v_textin,'K','C') into v_textin from dual ;
    dbms_output.put_line( 'Step 12 completed giving :' || v_textin );

    dbms_output.put_line( '[13] transcode M to N if not at start of name');
    SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'M','N') into v_textin from dual ;
    dbms_output.put_line('Step 13 completed giving :' || v_textin );

    dbms_output.put_line( '[14] transcode Q to G if not at start of name');
    SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Q','G') into v_textin from dual ;
    dbms_output.put_line('Step 14 completed giving :' || v_textin );

    dbms_output.put_line( '[15] transcode transcode SH to S ');
    SELECT REPLACE( v_textin,'SH','S') into v_textin from dual ;
    dbms_output.put_line('Step 15 completed giving :' || v_textin );

    dbms_output.put_line( '[16] transcode transcode SCH to S ');
    SELECT REPLACE( v_textin,'SCH','S') into v_textin from dual ;
    dbms_output.put_line('Step 16 completed giving :' || v_textin );

    dbms_output.put_line( '[17] transcode transcode YW to Y ');
    SELECT REPLACE( v_textin,'YW','Y') into v_textin from dual ;
    dbms_output.put_line('Step 17 completed giving :' || v_textin );

    dbms_output.put_line( '[18] if not first or last character, change Y to A');
    if length(v_textin) > 2 THEN
      SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2, length(v_textin) - 2),'Y','A') || SUBSTR ( v_textin , (length (v_textin))) into v_textin from dual ;
    end if;
    dbms_output.put_line('Step 18 completed giving :' || v_textin );

    dbms_output.put_line( '[19] transcode transcode WR to R ');
    SELECT REPLACE( v_textin,'WR','R') into v_textin from dual ;
    dbms_output.put_line('Step 19 completed giving :' || v_textin );

    dbms_output.put_line( '[20] if not first character, change Z to S ');
    if length(v_textin) > 1 THEN
      SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Z','S') into v_textin from dual ;
    end if;
    dbms_output.put_line('Step 20 completed giving :' || v_textin );

    dbms_output.put_line( '[21] transcode terminal AY to Y');
    if length(v_textin) > 1 THEN
      SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
      if v_sub = 'AY' THEN
        SELECT SUBSTR(v_textin, 1 , length(v_textin) - 2 ) || 'Y' into v_textin from dual ;
      end if;
    end if;
    dbms_output.put_line('Step 21 completed giving :' || v_textin );

    dbms_output.put_line( '[22] remove trailing vowels');
    LOOP
      SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
      dbms_output.put_line('Last letter :' || v_sub);
      if v_sub = 'A' THEN
        SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
        dbms_output.put_line('As last letter A drop last letter giving :' || v_textin || ' and check new last letter');
      else
        dbms_output.put_line('Last letter not A step 22 completed');
        EXIT;
      end if;
    END LOOP;
    dbms_output.put_line('Step 22 completed giving :' || v_textin );

    dbms_output.put_line( '[23] collapse all strings of repeated characters');
    if length(v_textin) > 2 THEN
      LOOP
        SELECT v_textin into v_sub from dual;
        SELECT REPLACE( v_textin,'AA','A') into v_textin from dual ;
        SELECT REPLACE( v_textin,'BB','B') into v_textin from dual ;
        SELECT REPLACE( v_textin,'CC','C') into v_textin from dual ;
        SELECT REPLACE( v_textin,'DD','D') into v_textin from dual ;
        SELECT REPLACE( v_textin,'FF','F') into v_textin from dual ;
        SELECT REPLACE( v_textin,'GG','G') into v_textin from dual ;
        SELECT REPLACE( v_textin,'HH','H') into v_textin from dual ;
        SELECT REPLACE( v_textin,'JJ','J') into v_textin from dual ;
        SELECT REPLACE( v_textin,'LL','L') into v_textin from dual ;
        SELECT REPLACE( v_textin,'NN','N') into v_textin from dual ;
        SELECT REPLACE( v_textin,'PP','P') into v_textin from dual ;
        SELECT REPLACE( v_textin,'RR','R') into v_textin from dual ;
        SELECT REPLACE( v_textin,'SS','S') into v_textin from dual ;
        SELECT REPLACE( v_textin,'TT','T') into v_textin from dual ;
        SELECT REPLACE( v_textin,'VV','V') into v_textin from dual ;
        SELECT REPLACE( v_textin,'WW','W') into v_textin from dual ;
        SELECT REPLACE( v_textin,'XX','X') into v_textin from dual ;
        SELECT REPLACE( v_textin,'ZZ','Z') into v_textin from dual ;
        dbms_output.put_line( v_textin );
      if v_sub = v_textin OR v_sub is NULL THEN
        EXIT;
      end if;
    END LOOP;
    end if;

    dbms_output.put_line('Step 23 completed giving :' || v_textin );


    dbms_output.put_line('[24] put back in first letter and return' );
    SELECT v_key || v_textin into v_textin from dual;

    RETURN( v_textin );

end NYSIIS;
/

NYSIIS function (an improvement on soundex)