Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Hamming Distance Algorithm in SQL

Here's a simple User-Defined Function implementation of the Hamming Distance Algorithm for SQL Server:

create function HamDist(@value1 char(8000), @value2 char(8000))
returns int
as
begin
    declare @distance int
    declare @i int
    declare @len int

    select @distance = 0,
           @i =1,
           @len = case when len(@value1) > len(@value2)
                       then len(@value1)
                       else len(@value2) end

    if (@value1 is null) or (@value2 is null)
        return null

    while (@i <= @len)
        select @distance = @distance +
                           case when substring(@value1,@i,1) != substring(@value2,@i,1)
                                then 1
                                else 0 end,
               @i = @i +1

    return @distance
end

Note that the inputs are cast to char(8000) so that the strings are padded to always be equal length if necessary, but we use the larger LEN() of the two values to ensure that we only check the necessary characters.  If either input is NULL, then NULL is returned.

This could be done set-based as well, but I am not sure if you will get much of a benefit  -- this short loop through the string values passed should be pretty efficient.

Enjoy!

Print | posted on Wednesday, May 09, 2007 10:39 AM | Filed Under [ T-SQL Code Library - SQL ]

Feedback

Gravatar

# re: Hamming Distance Algorithm in SQL

Jeff,
Thanks you. It is very simple. I tested with few examples and it gives correct result.
Regards,
Pinal Dave
5/14/2007 12:11 AM | Pinal Dave
Gravatar

# re: Hamming Distance Algorithm in SQL

i am a bs student doin computer engg .can u help me with programming ?
3/2/2008 1:01 PM | swati
Gravatar

# re: Hamming Distance Algorithm in SQL

Hi Jeff,
this is great. but just when i think where i coud really use it in real world applications, excuse my limited thoughts, i really couldnt fine one.
would there be any use case this alg. in SQL would solve or a business problem ?

It would be kind of you if you could help me here
12/16/2008 4:39 AM | karthik
Gravatar

# re: Hamming Distance Algorithm in SQL

I need User-Defined Function implementation of the Hamming Distance Algorithm for Postgresql. Can any one help me?
2/3/2009 2:18 PM | fayzur
Gravatar

# re: Hamming Distance Algorithm in SQL

thanks jeff, this article help me doin' my task :)
2/8/2009 8:06 PM | ria
Gravatar

# re: Hamming Distance Algorithm in SQL

Thanks, it really helped.
8/19/2010 4:11 AM | Alex
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET