September 24, 2003 at 12:05 pm
Using SQL Server 2000, the documentation says (that's for the XOR but same goes for the AND, OR and NOT ):
"Returns an int if the input values are int, a smallint if the input values are smallint, or a tinyint if the input values are tinyint....The ^ bitwise operator can be used only on columns of the integer data type category."
Nothing is said about the bitwise operators handling the bigint datatype.
And if you issue the following T-SQL command using Query Analyser:
SELECT ( (0 ^ 2147483648 ) | 0 )
with 2147483648 being a bigint and not an int, you get the result:
Server: Msg 403, Level 16, State 1, Line 2
Invalid operator for data type. Operator equals boolean XOR, type equals numeric.
However, the following script works (returning @value as-is):
declare @value as bigint
set @value = 2147483648
SELECT ( (0 ^ @value ) | 0 )
GO
Well, you can't always use local variables, especially if you want the bitwise operations to be part of your WHERE clause... so, you can use the following UDF, and it works as well:
CREATE FUNCTION dbo.BitWiseOnBigInts(@search bigint, @value bigint)
RETURNS bigint
AS
BEGIN
RETURN((@search ^ @value) | @search)
END
GO
declare @value as bigint
set @value = 2147483648
SELECT dbo.BitWiseOnBigInts(0, 2147483648)
GO
All these tests are between 0 and a bigint, but it works the same with 2 bigints.
So, it looks to me that despite what the documentation says and the obvious failure from the 1st test, bitwise operations on bigint work with the above 2 workarounds.
Am I missing something obvious, or is that a poorly documented feature?
September 24, 2003 at 12:44 pm
quote:
Am I missing something obvious, or is that a poorly documented feature?
Not very obvious, but you're missing the fact that a untyped value larger than an int will be evaluated as a numeric. Try this:
SELECT ( (0 ^ CAST(2147483648 AS bigint) ) | 0 )
--Jonathan
--Jonathan
September 25, 2003 at 2:49 am
thx - I thought I've tried that but I must have missed it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply