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)
, @ColumnList varchar(800)
, @MatchColumn varchar(80)
, @SearchFor varchar(150)
, @MaxSuggest varchar(3)
AS
Declare @strSQL varchar(1500)
Set @strSQL = 'SELECT ' + @ColumnList + ' FROM ' + @TableName +
' WHERE ' + @MatchColumn + ' = ''' + @SearchFor + ''''
exec (@strSQL)
IF @@ROWCOUNT<script></script> =0
Begin
Set @strSQL = 'SELECT ' + @ColumnList + ' FROM ' + @TableName +
' WHERE SOUNDEX(' + @MatchColumn +
') = SOUNDEX(''' + @SearchFor + ''')'
exec(@strSQL)
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)
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!