0

How to perform simple bit-wise operations like OR (|), AND (&), XOR (^) in SQL queries?

Can these be performed on numeric values? like

sel 1 | 2;
flag

1 Answer

1

Teradata doesn't have bitwise operations out of the box.

However if you can install a UDF pack, check out the Teradata UDFs for Byte Operations package.

Also, it is possible to do bit operations with the help of a table of powers of 2. Something like this:

    create table bitmask(i integer, mask integer);
    insert into bitmask(0,1);
    insert into bitmask(1,2);
    insert into bitmask(2,4);
    insert into bitmask(3,8);
    insert into bitmask(4,16);
    insert into bitmask(5,32);

    -- 1 | 2
    select coalesce(sum(a.mask),0) answer from bitmask a
    where 
        (1/a.mask) mod 2 = 1
    or (2/a.mask) mod 2 = 1;
Answer: 3

    -- 5 & 9
    select coalesce(sum(a.mask),0) answer from bitmask a
    where 
        (5/a.mask) mod 2 = 1
    and (9/a.mask) mod 2 = 1;
Answer: 1

    -- 5 XOR 9
    select coalesce(sum(a.mask),0) answer from bitmask a
    where 
        (5/a.mask) mod 2 <> (9/a.mask) mod 2;
Answer: 12
link|flag
Thanks Carlos. I'm limited with the usage of UDFs as the database is not our own and we only have select rights. – Monis Iqbal Jan 21 at 17:06

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.