Re: bit operations

From: "Johan Björk" <johan(at)websidorna(dot)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