[RESOLVED] SQL equivalent of CF's "BitAnd"

[WolfShade]

Hello, everyone.

Anyone, here, familiar with the ColdFusion "BitAnd" function? I'm looking for an MS-SQL equivalent (SQL Server 2003).

For those who are not familiar with CF, I'll try to explain.

Say I have a table that contains an ID, a region, and a bit value, and it looks like this:
regionTable:
ID RegionName BitValue
--------------------------------
1 North America 1
2 Central America 2
3 South America 4
4 Asia 8
5 Europe 16
6 Africa 32

I have another table with user information that includes regions of interest, using the combined bit values as a total:
userTable:
ID UserName regionInterest
-----------------------------------
1 Administrator 7 > interests are NA/CA/SA, 1+2+4 = 7
2 UserFriendly 14 > interests are CA/SA/Asia, 2+4+8 = 14
3 BloggerDude 13 > interests are NA/SA/Asia, 1+4+8 = 13

I'd like to be able to pull only those records where interests include, say, Central America, but I don't want to pull EVERY record and have CF sort it out (that would be inefficient.) But CF would compare by using BitAnd like this:

<cfif BitAnd(2,14)> <!--- code if 2 is in bit value of 14 ---><cfelse> <!--- otherwise ---></cfif>


Does MS-SQL have this ability?

Thanks,

^_^

[chazzy]

Does 2 & 14 work?

[WolfShade]

Does 2 & 14 work?

You mean is 2 a bit value of 14? Yes, 2+4+8 = 14. Is there an SQL BitAnd equivalent?

Thanks,

^_^

[chazzy]

No... i mean did you try a statement using the & operator? It's the sql server equivalent for bitwise and. ^ is the bitwise or.

[WolfShade]

No... i mean did you try a statement using the & operator? It's the sql server equivalent for bitwise and. ^ is the bitwise or.

Oh, sorry.. I'm in totally new territory, here. I know nothing of bitwise. Can you give me an example?

Thanks,

^_^

[chazzy]

Ok.. maybe I'm assuming a little too much.

The CF function BitAnd is the equivalent to (in sql terms at least) the bitwise operator of 'and'. I have a decent feeling you know what it means.

Concrete to your example, the following query should give you some insight..


select r.regionName, u.userName
from regionTable r, userTable u
where r.BtiValue & u.regionInterest

[WolfShade]

So, there really isn't any kind of function that needs to be called? You just do a 'WHERE x & y' and it will check to see if the second value is a bit of the first value??

That just seems way too simple. Am I misunderstanding?? :)

^_^

[chazzy]

So, there really isn't any kind of function that needs to be called? You just do a 'WHERE x & y' and it will check to see if the second value is a bit of the first value??

That just seems way too simple. Am I misunderstanding?? :)

^_^

Actually, your reaction should be "Why is it in CF that it's a function, and everywhere else uses the & and ^ operators?" :D

Don't forget, bitwise and and bitwise or are essentially on the order of complexity of mathematical operators (+, -, /, *)

Edit: Maybe some extra explanation would help as well.

The app's not really looking at 2 or 14, instead their values expressed as powers of 2.

So 2 = 2^1 or 1
and 14 = 2^1 + 2^2 +2^3

And & handles sets, so those values become {1} & {1,2,3} and it's essentially checking to see if 1 is contained in 1,2,3.

[WolfShade]

Edit: Maybe some extra explanation would help as well.

The app's not really looking at 2 or 14, instead their values expressed as powers of 2.

So 2 = 2^1 or 1
and 14 = 2^1 + 2^2 +2^3

And & handles sets, so those values become {1} & {1,2,3} and it's essentially checking to see if 1 is contained in 1,2,3.
Ah! Thanks for the detailed explanation, that makes so much sense.

But I am getting an error message. I think it's because I'm trying to use a variable or hard-coded number instead of comparing between two table INT values. The query is for a directory listing of all users on the site, and the client would like to have the ability to filter the resulting recordset by either region or key interest. So, going with the region, first, is what I am trying to do; then do the same for key interest.

When I tried running it in QA with a hard coded '1' for North America, I got the following:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.

SELECT U.userID, U.title, U.firstName, U.midName, U.lastName, U.suffix, UR.regionName, U.city, U.state,
U.postal, U.country, U.email, U.otherUserContact, U.accountCreated
FROM Users U LEFT JOIN UsersRegion UR on UR.regionID = U.region
WHERE 1 & U.regionsInterestValue
ORDER BY UR.regionID

If I take out the ORDER BY, which I need but removed for testing, I get this:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'regionsInterestValue'.

SELECT U.userID, U.title, U.firstName, U.midName, U.lastName, U.suffix, UR.regionName, U.city, U.state,
U.postal, U.country, U.email, U.otherUserContact, U.accountCreated
FROM Users U LEFT JOIN UsersRegion UR on UR.regionID = U.region
WHERE 1 & U.regionsInterestValue

Will this work with a variable that will be passed to the stored procedure? Or is it only for comparing between columns?

Thanks,

^_^

[chazzy]

Ok.

1. What type is regionsInterestValue?
2. Does it work if you don't use the hard coded value and just join between the two columns?

[WolfShade]

1. Tinyint
2. Yes, as long as I take out the WHERE, it will grab every record in the table.

[chazzy]

Ok, let me back up a bit.

Did this concrete example work or no?

select r.regionName, u.userName
from regionTable r, userTable u
where r.BtiValue & u.regionInterest


If yes - try having both columns the same int type (1 as a literal is interpretted as integer, not tinyint)

If no - what about this statement?


select r.regionName, u.userName
from regionTable r, userTable u
where r.BitValue & u.regionInterest = r.BitValue

[WolfShade]

If no - what about this statement?


select r.regionName, u.userName
from regionTable r, userTable u
where r.BitValue & u.regionInterest = r.BitValue


THIS ONE DID IT. I had to modify the column names, but it worked. Waitaminnit.. let me double check to make sure it's getting the correct records..

Okay, this is getting all users who have expressed an interest in ANY region and ignores those who have not selected a region of interest, so at its base it is working great! Now I just need to set it so it will accept a parameter for specifically one region - shouldn't be a problem for me to figure it out, but don't be surprised if you see me back here with a few more questions.

Thanks, chazzy, for helping me out with this one - you rock!

Also, I had changed the tinyint to int per your suggestion, and am sure that had a lot to do with it, as well.

^_^

[chazzy]

select r.regionName, u.userName
from regionTable r, userTable u
where r.BitValue & u.regionInterest = 1

[WolfShade]

Here is code for filter by Key Issue:

SELECT U.topicsInterestValue, U.userID, U.title, U.firstName, U.midName, U.lastName, U.suffix, UI.interestName, U.city, U.state,
U.postal, U.country, U.email, U.otherUserContact, U.accountCreated, UR.regionName
FROM UserInterests UI, Users U JOIN UsersRegion UR ON UR.regionID = U.region
WHERE UI.interestValue & U.topicsInterestValue = UI.interestValue AND UI.interestValue = @passedVariable
ORDER BY UR.regionID

Here is code for filter by Region Interest:
SELECT U.regionsInterestValue, U.userID, U.title, U.firstName, U.midName, U.lastName, U.suffix, UR.regionName, U.city, U.state,
U.postal, U.country, U.email, U.otherUserContact, U.accountCreated
FROM UsersRegion UR, tUsers U
WHERE UR.regionBit & U.regionsInterestValue = UR.regionBit AND UR.regionBit = @aVariable
ORDER BY UR.regionID
GO


Your help really clinched it, razzy. Much appreciated.

^_^

[chazzy]

assuming that the variables are integer types, you should be able to replace part of those queries, and use these:


SELECT U.topicsInterestValue, U.userID, U.title, U.firstName, U.midName, U.lastName, U.suffix, UI.interestName, U.city, U.state,
U.postal, U.country, U.email, U.otherUserContact, U.accountCreated, UR.regionName
FROM UserInterests UI, Users U JOIN UsersRegion UR ON UR.regionID = U.region
WHERE UI.interestValue & U.topicsInterestValue = @passedVariable
ORDER BY UR.regionID