CODEWITHSUNDEEP
mysql
Leander
Leander

Reputation: 1395

get count of leading zeros in mysql

Is it possible to create a select query that computes the number of leading zeros from a bit-operation in mysql? I would need to compare this to a threshold and return the results. The Select-Query would be called very often, but i think that every SQL-based solution is better than our current strategy of loading everything into memory and then doing it in java.

example of the functionality:

unsigned INT with the value 1 -> 31, since there 32 bits and the rightmost is set unsigned INT with the value 0x0C000000 -> 4, there are 4 zero bits from the highest order to the first set

I can then compare the result to a threshold an only get the ones above the threshold.

Pseudocode example query:

SELECT *
FROM data
WHERE numberOfLeadingZeros(data.data XOR parameter) >= threshold;

Upvotes: 0

Views: 1220

Answers (2)

puelo
puelo

Reputation: 6037

This should work, but please take a look at @viraptor's suggestion:

SELECT * FROM data WHERE (32 - LENGTH(BIN(data))) >= threshold

This just converts the integer into a binary string without the leading zeros. So if you subtract the length of this string from 32 you got the amount of leading zeros in the number.

Fiddle: http://sqlfiddle.com/#!9/e56c31/2

Upvotes: 1

viraptor
viraptor

Reputation: 34195

You can ignore the counting of zeros, and just compare to a threshold. For example you know how many leading 0s are in 32-bit value 2. Anything with fewer 0s will be >2 and anything with more 0s will be <2.

So for your solution, instead of doing query number_of_zeros(x) >= threshold do a query for x < value_with_threshold_leading_zeros. (the value being 2^(31-number_of_zeros))

Upvotes: 1

Related Questions