Simplified phonetic search with MS SQL Server

Introduction

I recently found out about the phonetic search feature of SQL Server, and I have implemented a sample of its usage here.

Background

Something very interesting about the Soundex is, the algorithm for matching got patent in 1917, I think even before the invention of electronic calculator or close to that of the light-bulb. Checkout http://www.creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm.

Using the code

The Stored Procedure accepts the table name, a comma separated column list as a result list, the name of the column searched for as MatchColumn, a search string to search for, and a maximum number of suggestions in case of no exact match found.

The Stored Procedure checks for matching records in the table passed as a parameter, and acts as:

• Exact Match found - returns a result set with all matching records in the provided column list fashion.
• No Match Found - performs phonic search and returns two more result sets: first as an exact match (no records), second as matching records using phonic search, and third as a maximum specified suggestion along with the column name "Similarity", where the rule is - higher the similarity value, more close the result is to the search keyword.

I tried the sample on the Northwind database and searched for "AANA" and got "Ana Trujillo" and "Ann Devon", quite close.

Usage

Here is how you use the Stored Procedure:

Exec PhonicSearch 'Customers', 'customerid, CompanyName, 
     ContactName, ContactTitle'<script></script> , 'ContactName', 'AANA',5 

Here is the code for the Stored Procedure:

CREATE PROCEDURE PhonicSearch 
@TableName varchar (80)  -- table name
, @ColumnList varchar(800) -- list of comma seperated column list to return
, @MatchColumn varchar(80) -- compare column with
, @SearchFor varchar(150) -- search string to compare with @MatchColumn
, @MaxSuggest varchar(3) -- in case no exact match found the maximum number 
                         -- of suggested value in @MatchColumn column<script></script> 

AS   

Declare @strSQL varchar(1500)

-- Build and execute SQL query to find exact match 
Set @strSQL = 'SELECT ' + @ColumnList + ' FROM ' + @TableName + 
              ' WHERE ' + @MatchColumn + ' = ''' + @SearchFor + ''''
--Print @strSQL
exec (@strSQL)

-- If no exact match found build query with phonic search 
IF @@ROWCOUNT<script></script> =0 
Begin 
    -- Phonic Search query   
    Set @strSQL = 'SELECT ' + @ColumnList + ' FROM ' + @TableName + 
                  ' WHERE SOUNDEX(' + @MatchColumn + 
                  ') = SOUNDEX(''' + @SearchFor + ''')'

    exec(@strSQL)
    --print @strSQL

    -- Suggetested words for refin search 
    Set @strSQL = 'SELECT TOP ' + @MaxSuggest + ' ' + @MatchColumn + ', DIFFERENCE('+ 
                  @MatchColumn+ ', '''<script></script> + @SearchFor + 
                  ''') as Similarity FROM ' + @TableName + ' WHERE SOUNDEX(' + 
                  @MatchColumn + ') = SOUNDEX(''' + @SearchFor + 
                  ''') ORDER BY Similarity'

    exec(@strSQL)
    --print @strSQL
END

Points of Interest

Visit http://www.creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm for more information.

Question: how come they named it as "Phonetic search" when even the telephone was not available???

History

None yet!