MinhTech.com

Yet another technology tutorial blog.

Oracle Soundex Incorrect

By • Jul 5th, 2009 • Category: Oracle

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

is a technology junkie.
Email this author | All posts by

2 Responses »

  1. Oh thank you!

  2. Depends if you are after ‘Knuth’ soundex, or NARA soundex, really. So ‘wrong’ is quite a strong term.

Leave a Reply