Click here to Skip to main content
13,355,093 members (63,401 online)
Click here to Skip to main content
Add your own
alternative version


18 bookmarked
Posted 1 Nov 2007

Simplified phonetic search with MS SQL Server

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


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


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

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.


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:

@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> 


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 
    -- Phonic Search query   
    Set @strSQL = 'SELECT ' + @ColumnList + ' FROM ' + @TableName + 
                  ' WHERE SOUNDEX(' + @MatchColumn + 
                  ') = SOUNDEX(''' + @SearchFor + ''')'

    --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'

    --print @strSQL

Points of Interest

Visit for more information.

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


None yet!


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


About the Author

Web Developer
India India
Nothing much

You may also be interested in...


Comments and Discussions

GeneralTried to find more info about the patent Pin
CitizenDC29-Apr-08 0:48
memberCitizenDC29-Apr-08 0:48 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 1 Nov 2007
Article Copyright 2007 by vini2k1
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid