Use SOUNDEX() word by word on SQL Server

sql sql-server soundex
Charles picture Charles · Dec 17, 2009 · Viewed 28.3k times · Source

Here is my problem. For example I have a table Products that contains a field, Name:

Products
ID | Name | ..
1  | "USB Key 10Go"
2  | "Intel computer"
3  | "12 inches laptop computer"
...

I'm currently implementing a simple search engine (SQL Server and ASP .NET, C#) for an iPhone web-app and I would like to use the SOUNDEX() SQL Server function.

The thing is, I can't directly use SOUNDEX on the Name field. (This would be irrelevant since there are several words in the name.) I would like to apply the SOUNDEX function to each word from the Name field, and then see if any of them matches the researched keyword.

If someone has any clue how to do this, that would be awesome.

Answer

Bob Jarvis - Reinstate Monica picture Bob Jarvis - Reinstate Monica · Dec 17, 2009

Rather than use Soundex you might be better off computing the Levenshtein distance between the two strings. See the Wikipedia article on Levenshtein distance.

There's a TSQL implementation of the Levenshtein distance algorithm here.

Share and enjoy.

EDIT 03-May-2012

Since writing my original response I've learned that Oracle includes the Levenshtein distance and several other "string similarity" functions in the UTL_MATCH package, which I believe is a standard part of the database. Documentation here. Perhaps not directly related to the original post (which was for SQL Server) but perhaps useful as many shops use multiple databases.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More