From: "Johan Björk" <johan@websidorna.com>
> Have a little problem with bit operaitions that I cannot find the answer
to on the Internet. Have been searching through the archives but no result.
>
> In MySQL you can have a INT column and do bit logic ala C-style like this:
> "select * from table where flags & 4;"
If you're using an int for "flags":
select * from table where (flags & 4) > 0;
The bitwise AND returns an integer rather than a boolean, and AFAIK SQL
doesn't follow C's rules on this.
> Can I do something similar with std SQL? I've been trying to cast
everything to BIT but without success, and I've also been creating a "flags
bit(4)", setting a row to "1000" (8) and trying to compare, but I have
absolutely no idea how to.
>
> Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?!
To check two values just do:
SELECT * FROM table WHERE (flags & val1 & val2) > 0;
If you want to use BIT types you'll need something like:
SELECT * FROM table WHERE (flags & '0010100'::BIT) <> '0'::BIT;
and updates like
UPDATE table SET flags = flags | '0001000'::BIT;
Note the need to have the same string-length when using AND/OR.
HTH
- Richard Huxton