Binaiy query 1

[elck]

Hello,

I have put a number of options into one binary number.
Now I am looking for a query that selects rows that have one option, Not have another option while a third option is irrelevant.


For instance:
If a bicycle has handbrakes, I OR the options with 4
If it has a rear light I or it with 2
And if it has a headlight I or it with 1

How do I select all bikes that:
Have a rear light, NOT have handbrakes and I do not care wether they have a headlight or not?

Would that go something like this?
$andmask=1 OR 4
$resultmask=1
SELECT * FROM `bikes` WHERE `options` AND $andmask = $resultmask

 

[TonyGroves]

You could use the BIN function:
[tt]
WHERE BIN(options) LIKE '%01_'
[/tt]
This will match if the binary representation of the field has a 1 in position 1, and a 0 in position 2.

 

[elck]

Ok, but what if I am looking for a 1 in the middle of the binary number???
There does not seem to be a 'one-character-wildcard'

WHERE BIN(options) LIKE '??010?0010'

BTW, I have great diffuclty finding a good explanation of WHERE and LIKE syntax in the manual, isn't there a Good reference manual somewehre?

 

[TonyGroves]

The LIKE (and WHERE) syntax is well explained in the MySQL manual. An underscore represents a single character, a % represents any number of characters. Therefore LIKE '%01_' matches a string where position 0 (value 1) is a character of any value, position 1 (value 2) contains a 1, position 2 (value 4) contains a 0, and higher positions, if any, can contain anything. And your latest example could probably be represented by LIKE '%010_0010' .

 

[TonyGroves]

Another thought - leading zeroes will be dropped by the BIN function. Therefore, instead of just BIN(options), you would need to restore leading zeroes by using something like LPAD(BIN(options),32,0), where 32 is the number of bits required.

 

[elck]

Ok, great that is exactly what I needed.

Could you provide me with a link to the page in the manual where these things are explained? The search option always leads me up a blind alley and the index doesn't do much better.
It probably is a matter of being acustomed to the structure, but I have spend (too) much time searching.

Thanks,

Elck

 

[TonyGroves]

It's under Functions & Operators -> Functions -> String Functions -> String Comparison Functions.