# Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

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

## 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 ]

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

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

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

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

## #re: Hamming Distance Algorithm in SQL

2/8/2009 8:06 PM | ria

## #re: Hamming Distance Algorithm in SQL

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