Geek Answers Handbook

MSSQL BIT_COUNT (Hammingdistance)

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 settings.

Here is what I have for MYSQL:

 CREATE FUNCTION `HAMMINGDISTANCE`(`hasha` BIGINT, `hashb` BIGINT) RETURNS int(11) DETERMINISTIC RETURN BIT_COUNT(hasha^hashb)
+6
source share
3 answers

Why not just write your own bit_count code in T-SQL? There is no need to use SQL CLR if you only need to count the number of bits in 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 fiddle you can try to see this function in action.

It should be remembered that this algorithm only works on non-negative figures. If you are looking for an algorithm that works with negative icons, see this link .

+5
source

I just wrote HAMMINGDISTANCE using the provided dcp code 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
+2
source 
 CREATE FUNCTION [dbo].[fn_hamming_distance] ( @a BIGINT, @b BIGINT ) RETURNS TINYINT AS BEGIN DECLARE @c BIGINT = @a ^ @b RETURN CASE WHEN @c IS NULL THEN NULL ELSE CASE WHEN @c < 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x4000000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x2000000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x1000000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x800000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x400000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x200000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x100000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x80000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x40000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x20000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x10000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x8000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x4000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x2000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x1000000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x800000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x400000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x200000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x100000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x80000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x40000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x20000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x10000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x8000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x4000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x2000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x1000000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x800000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x400000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x200000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x100000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x80000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x40000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x20000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x10000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x8000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x4000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x2000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x1000000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x800000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x400000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x200000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x100000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x80000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x40000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x20000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x10000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x8000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x4000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x2000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x1000 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x800 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x400 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x200 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x100 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x80 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x40 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x20 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x10 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x8 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x4 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x2 > 0 THEN 1 ELSE 0 END + CASE WHEN @c & 0x1 > 0 THEN 1 ELSE 0 END END END
0
source

More articles:


All Articles