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
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!
Legacy Comments
|
Pinal Dave
2007-05-14 |
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 |
|
swati
2008-03-02 |
re: Hamming Distance Algorithm in SQL i am a bs student doin computer engg .can u help me with programming ? |
|
karthik
2008-12-16 |
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 |
|
fayzur
2009-02-03 |
re: Hamming Distance Algorithm in SQL I need User-Defined Function implementation of the Hamming Distance Algorithm for Postgresql. Can any one help me? |
|
ria
2009-02-08 |
re: Hamming Distance Algorithm in SQL thanks jeff, this article help me doin' my task :) |
|
Alex
2010-08-19 |
re: Hamming Distance Algorithm in SQL Thanks, it really helped. |