Looking for query equivalent

To extract a certain type of information we use a query like this in Oracle:

WHERE MOD(TRUNC(ACCOUNT_TYPE / POWER(2,0)),1) > 0

I am hoping someone can help me with the equivalent query syntax for sql server

Scott

Does POWER(2,0) mean "two to the zero power"? If so, why not just write 1? It appears that POWER(2,x) mean the same in both dialects.

Does MOD(x,1) mean MODULUS? Are you wanting the fractional part of x? If so try x - floor(x)

I am guessing that floor(x) is the same as TRUNC(x)

The "Modulus" operator in SQL server is the "%" operator; for instance 17%3 = 2; 21%10=1

Give a look to "Mathematical Functions" in books online

(Somebody check me, please)

|||

Do would it look something like this:

MOD(m,n) - where mod returns the remainder of m divided by n

TRUNC(num, x) - A number is truncated to x decimal places

Power(x,y) - x raised to the y power

Oracle Version: MOD(TRUNC(ACCOUNT_TYPE / POWER(2, $x)), 2) > 0

SQL Version: floor(ACCOUNT_TYPE / POWER(2, $x))% 2) > 0

Maybe?

|||

If your looking to deterine the xth bit, probably:

set nocount on
declare @.mockUp table
( ACCOUNT_TYPE integer,
powerOfTwo tinyint
)
insert into @.mockUp values (29, 3)
insert into @.mockUp values (14, 0)
insert into @.mockUp values (3, 2)
insert into @.mockup values (900, 7)
insert into @.mockup values (625, 5)
insert into @.mockup values (convert(int, 0xffffffff), 1)
insert into @.mockup values (convert(int, 0xffffffff), 2)
insert into @.mockup values (convert(int, 0xffffffff), 3)
insert into @.mockup values (convert(int, 0xffffffff), 4)
insert into @.mockup values (convert(int, 0xffffffff), 5)
insert into @.mockup values (convert(int, 0xffffffff), 6)
insert into @.mockup values (convert(int, 0xffffffff), 7)
insert into @.mockup values (convert(int, 0xffffffff), 8)
insert into @.mockup values (convert(int, 0xffffffff), 9)
insert into @.mockup values (convert(int, 0xffffffff), 10)
insert into @.mockup values (convert(int, 0xffffffff), 11)
insert into @.mockup values (convert(int, 0xffffffff), 12)
insert into @.mockup values (convert(int, 0xffffffff), 13)
insert into @.mockup values (convert(int, 0xffffffff), 14)
insert into @.mockup values (convert(int, 0xffffffff), 15)
insert into @.mockup values (convert(int, 0xffffffff), 16)
insert into @.mockup values (convert(int, 0xffffffff), 17)
insert into @.mockup values (convert(int, 0xffffffff), 18)
insert into @.mockup values (convert(int, 0xffffffff), 19)
insert into @.mockup values (convert(int, 0xffffffff), 20)
insert into @.mockup values (convert(int, 0xffffffff), 21)
insert into @.mockup values (convert(int, 0xffffffff), 22)
insert into @.mockup values (convert(int, 0xffffffff), 23)
insert into @.mockup values (convert(int, 0xffffffff), 24)
insert into @.mockup values (convert(int, 0xffffffff), 25)
insert into @.mockup values (convert(int, 0xffffffff), 26)
insert into @.mockup values (convert(int, 0xffffffff), 27)
insert into @.mockup values (convert(int, 0xffffffff), 28)
insert into @.mockup values (convert(int, 0xffffffff), 29)
insert into @.mockup values (convert(int, 0xffffffff), 30)

select ACCOUNT_TYPE,
powerOfTwo,
floor(ACCOUNT_TYPE / POWER(2, powerOfTwo))% 2 as [floor routine],
(ACCOUNT_TYPE & POWER(2, powerOfTwo))/power(2, powerOfTwo) as [Masked routine]
from @.mockUp

-- ACCOUNT_TYPE powerOfTwo floor routine Masked routine
-- - - --
-- 29 3 1 1
-- 14 0 0 0
-- 3 2 0 0
-- 900 7 1 1
-- 625 5 1 1
-- -1 1 0 1
-- -1 2 0 1
-- ...
-- -1 29 0 1
-- -1 30 0 1