MinhTech.com

Oracle’s implementation of Soundex is incorrect.

Test the Function:

SQL> select soundex('Ashcraft')
from dual;

A226

Ashcraft should return A261 and not A226.

Create the Function:

SQL> create or replace function soundexxcode (character varchar2) return number is
   v_digit number(1,0);
begin
   case
      when character in ('B', 'F', 'P', 'V') then v_digit := 1;
      when character in ('C', 'G', 'J', 'K', 'Q', 'S', 'X', 'Z') then v_digit := 2;
      when character in ('D', 'T') then v_digit := 3;
      when character in ('L') then v_digit := 4;
      when character in ('M', 'N') then v_digit := 5;
      when character in ('R') then v_digit := 6;
      when character in ('H', 'W') then v_digit := 0;
      else v_digit := NULL;
   end case;

   return v_digit;
end soundexxcode;
/

Function created.

SQL> create or replace function soundexx (string varchar2) return varchar2 is
   v_digit number(1, 0);
   v_digit_last number(1, 0);
   v_soundex varchar2(64);
   v_string varchar2(64);
begin
   v_string := upper(string);
   v_soundex := substr(v_string, 1, 1);
   v_digit_last := soundexxcode(v_soundex);

   for i in 1 .. length(v_string) loop
      v_digit := soundexxcode(substr(v_string, i, 1));

      if v_digit > 0 And v_digit <> nvl(v_digit_last, 0) then
         v_soundex := v_soundex || v_digit;
      end if;

      if nvl(v_digit, 0) <> 0 then
         v_digit_last := v_digit;
      end if;
   end loop;

   return rpad(v_soundex, 4, '0');
end soundexx;
/

Function created.

Test the New Function:

SQL> select soundexx('Ashcraft')
from dual;

A261