Click here to Skip to main content
Click here to Skip to main content

Simplified phonetic search with MS SQL Server

, 1 Nov 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Perform phonetic search on MS SQL Server using Soundex.

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

vini2k1
Web Developer
India India
Nothing much

Comments and Discussions

 
GeneralTried to find more info about the patent PinmemberCitizenDC29-Apr-08 0:48 
GeneralPhonetic PinmemberHeyYouzz1-Nov-07 23:25 
GeneralRe: Phonetic Pinmembervini2k12-Nov-07 4:13 
QuestionPlease, can you reformat your article ? PinmemberThierry Maurel1-Nov-07 22:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 1 Nov 2007
Article Copyright 2007 by vini2k1
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid