MSSQL BIT_COUNT (Hammingdistance)

Question

Is there any function similar to the MYSQL BIT_COUNT function in MSSQL? I want to create a very simple Hammingdistance function in MSSQL that i can use in my selects.

Here is what i have for MYSQL:

CREATE FUNCTION `HAMMINGDISTANCE`(`hasha` BIGINT, `hashb` BIGINT)
    RETURNS int(11)
    DETERMINISTIC
    RETURN 
    BIT_COUNT(hasha^hashb)

Answer 1

Why not just write your own bit_count code in T-SQL? There's no need to use SQL CLR if all you need is to count the number of set bits in a bigint. Here is an example:

CREATE FUNCTION bit_count
(
   @pX bigint
)
RETURNS int
AS
BEGIN
   DECLARE @lRet integer
   SET @lRet = 0
   WHILE (@pX != 0)
   BEGIN
      SET @lRet = @lRet + (@pX & 1)
      SET @pX = @pX / 2
   END   
   return @lRet   
END
GO

Also, here's a fiddle you can try to see this function in action.

Be advised that this algorithm only works on non-negative bigints. If you are looking for an algorithm that works on negative bigints, see this link.

Answer 2

I just wrote a HAMMINGDISTANCE using the provided code by dcp and it seems to work.

CREATE FUNCTION [dbo].[HAMMINGDISTANCE](@hasha BIGINT, @hashb BIGINT)
    RETURNS int
AS
BEGIN
   DECLARE @pX BIGINT
   DECLARE @lRet integer
   SET @lRet = 0
   SET @pX = @hasha ^ @hashb
   WHILE (@pX != 0)
   BEGIN
      SET @lRet = @lRet + (@pX & 1)
      SET @pX = @pX / 2
   END   
   return @lRet   
END

GO