CODEWITHSUNDEEP
sqlsql-serverhamming-distance
David Hill
David Hill

Reputation: 13

How to Compute Hamming Distance of two VARCHAR's in SQL Server?

In my table, I have two columns of the Datatype 'Varchar' with the same length. These columns are binary representations of information, and I would like to select the Hamming Distance between them, but I can't find any function to do that. Does anyone have any guidance?

Upvotes: 0

Views: 262

Answers (1)

Martin Smith
Martin Smith

Reputation: 453898

Create an auxiliary numbers table with unique sequential integers at least up until the max string length and then you can do

SELECT CASE WHEN LEN(@String1) = LEN(@String2) THEN COUNT(*) END
FROM   dbo.Numbers
WHERE  Number BETWEEN 1 AND LEN(@String1)
       AND SUBSTRING(@String1, Number, 1) <> SUBSTRING(@String2, Number, 1)

In future product versions the auxiliary numbers table can be swapped out by the GENERATE_SERIES function.

NB: If you are planning on running this calculation over many strings - e.g. to return the row with the closest Hamming Distance you'll likely find performance an issue though...

Upvotes: 1

Related Questions