Comparing binary values in MySQL

Question

Say you have two binary values

001011 
001111

How can you get the number of different bits in MySQL? I tried

SELECT BIT_COUNT(BINARY  001011 ^ BINARY 001111)

This returns 6, while I need a solution that returns 1 in this example.

score 3 · Accepted Answer · answered Aug 18 '11 at 17:48

3

SELECT BIT_COUNT( CONV( '001011', 2, 10 ) ^ CONV( '001111', 2, 10 ) )

answered Aug 18 '11 at 17:48

nobody

How about for large binary strings? It seems to cap out at 2^16 or thereabout. – Sheqel Aug 18 '11 at 18:11
I'm using MySql 5.1 and it doesn't seem to have a cap `SELECT CONV( '11111111111111111111111111111111', 2, 10 )` returns `4294967295` as expected. – nobody Aug 18 '11 at 18:17
I'm trying this example: SELECT BIT_COUNT( CONV( 1111111110011111000001000000111000000110100011001111011101010100, 2, 10 ) ^ CONV( 1111111110011111000001000001111000000110100011010000000110010000, 2, 10 ) ) (there are a couple of bits different in the middle somewhere, but it returns 0) thanks again. – Sheqel Aug 18 '11 at 18:30
@Sheqel After doing some tests I figured out there is a 63bit limit, I suppose it's related to the sign bit. BTW put the first argument between quotes or it will be interpreted as a base10 number. – nobody Aug 18 '11 at 19:03

score 3 · Answer 2 · answered Aug 18 '11 at 17:51

3

SELECT BIT_COUNT(b'001011' ^ b'001111');

answered Aug 18 '11 at 17:51

coyotebush

score 0 · Answer 3 · answered Aug 18 '11 at 17:53

0

It's converting the numbers 1011 and 1111 (base 10) to binary and doing the comparison. If you did:

SELECT BIT_COUNT(11 ^ 15)

It'd work.

answered Aug 18 '11 at 17:53

James

1

actually, the leading 0's make MySQL see those as octal numbers. – Marc B Aug 18 '11 at 17:54
How about for large binary strings? It seems to cap out at 2^16 or thereabout. – Sheqel Aug 18 '11 at 18:12
Didnt know that mysql converted 0nnn to octal. Thanks for enlightening me. – James Aug 18 '11 at 18:34

3 Answers3