General

SQL Phonetic Functions

From IT Jungle:

You can use phonetic functions to select or order rows based on the phonetic sound of a string as opposed to the actual characters in the string. The obvious use of phonetic functions is with names, but they can be used with any string columns.

I must admit that this touches on one of my pet peeves — the spelling of my surname. I have lost count of the number of times I have had to spell my name two, three, or four times for someone on the phone — and they still get it wrong. (Why is it that when I spell TUO, they hear TOU?) I have also lost count of the number of times I have had to dig out an account number because I “don’t appear to have an account.” All of this could be avoided if the person at the other end was using a “fuzzy” search with a phonetic function.

In order to demonstrate the use of phonetic functions, I created the following table:

CREATE OR REPLACE TABLE PHONETIC (
TESTNO INTEGER NOT NULL DEFAULT ,
BASE VARCHAR(20) NOT NULL DEFAULT,
NAME VARCHAR(20) NOT NULL DEFAULT );

There will be a number of rows for a test (identified by TESTNO). Each row, for a test, will have the same BASE value and a different value for NAME.

Before getting into the nitty gritty, you should be aware that phonetic functions may not give you what you expect. There are so many variables to take into account — language, national pronunciation, and regional pronunciation, to name a few. Therefore, you should not treat the result of a phonetic function in the same way as you would standard column values.

(Read more)