Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Simplified phonetic search with MS SQL Server

1.00/5 (1 vote)
1 Nov 2007CPOL1 min read 1  
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:

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

Here is the code for the Stored Procedure:

SQL
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)