CODEWITHSUNDEEP
mysqlbit-manipulationbitbitwise-operators
Umakant Patil
Umakant Patil

Reputation: 2308

Mysql select rows where a specific bit is set of an integer

I need a field where I can set multiple values. I don't want to go ahead with mysql sets. The other way round I can do is say I have multiple categories.

cat 1, cat 2, cat 3....etc. limited to some 20 categories. Assume them as 1 (00001), 2(00010), 4(00100)...etc.

If my post is in 'cat 1' I would add value of 1(00001) in the post table field which is TINYINT. If my post in in 'cat 1' and 'cat 2' I would add value of 3 (00011) in the TINYINT field.

Now I want to get all the posts which are in 'cat 1' I want to query like field='****1' i.e. is just to check if last bit is on.

One more example. Say my post is in 'cat 2' and 'cat 3', I would add the value 6 (00111). Now I want to get all the posts which are in 'cat 2'. I would like to query where field='***1*'.

As above examples I would like to know how to query a integer field based on which bit is on?

Upvotes: 2

Views: 906

Answers (2)

Conqueror Rose
Conqueror Rose

Reputation: 1

SELECT CONV(15,10,2);

or

SET @SAYI = 86;
SELECT
    @SAYI SAYI,
    CONCAT(
        TRUNCATE(@SAYI/64,0), 
        TRUNCATE(MOD(@SAYI,64)/32,0),
        TRUNCATE(MOD(MOD(@SAYI,64),32)/16,0), 
        TRUNCATE(MOD(MOD(MOD(@SAYI,64),32),16)/8,0),
        TRUNCATE(MOD(MOD(MOD(MOD(@SAYI,64),32),16),8)/4,0),
        TRUNCATE(MOD(MOD(MOD(MOD(MOD(@SAYI,64),32),16),8),4)/2,0),
        TRUNCATE(MOD(MOD(MOD(MOD(MOD(MOD(@SAYI,64),32),16),8),4),2)/1,0)
    ) AS BINARY_;

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Not played with bit strings in years (used to store a hell of a lot of info in them back in mainframe days).

However you should be able to use bit arithmetic. For example to check the 2nd bit is on:-

SELECT *
FROM sometable
WHERE bit_field & 2 = 2

Or to check the 2nd bit is on and the 1st bit is off:-

SELECT *
FROM sometable
WHERE bit_field & 3 = 2

Few details here:-

http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html

Upvotes: 2

Related Questions