Thursday, May 22, 2014

Soundex

I found a booklet called Introduction to SQL Version 1.0, printed in 1985 and 1989. It was written by a certain Lawrence Ellison and described a function called SOUNDEX, which I had never heard of before so I tried it out on an Oracle 9 database.

SOUNDEX represents the sound of a word by a 4 character alphanumeric code. Words which sound alike should have the same code. This allows you to select rows containing data with a particular sound. Sometimes it works:
 
SQL> select soundex('STEWART') from dual;
 
SOUN
----
S363
 
SQL> select soundex('STUART') from dual;
 
SOUN
----
S363
 
SQL>
 
SQL> select soundex('BORED') from dual;
 
SOUN
----
B630
 
SQL> select soundex('BOARD') from dual;
 
SOUN
----
B630
 
SQL>
 
But sometimes it gives words, which sound the same, different codes:
 
SQL> select soundex('WITCH') from dual;
 
SOUN
----
W320
 
SQL> select soundex('WHICH') from dual;
 
SOUN
----
W200
 
SQL>
 
And sometimes it gives words, which sound different, the same code:
 
SQL> select soundex('FLOOR') from dual;
 
SOUN
----
F460
 
SQL> select soundex('FLOUR') from dual;
 
SOUN
----
F460
 
SQL>

No comments: