General UDFs to emulate BITTEST(), BITSET() ?
-
Can anyone point me to UDF's that emulate these useful functions in other systems (e.g. VFP)?
BITTEST(nExpression1, nExpression2)
- nExpression1
- Specifies the numeric value in which a bit is checked. .
- nExpression2
- Specifies the bit position in nExpression1 that is checked. nExpression2 can range from 0 to 31; 0 is the rightmost bit
- Returns the value of the bit.
BITSET(nExpression1, nExpression2)
Sets the bit to 1 in a numeric value and returns the resulting value.
- nExpression1
- Specifies the numeric value in which a bit is set.
nExpression2
- Specifies the bit position in nExpression1 that is set to 1. nExpression2 can range from 0 to 31; 0 is the rightmost bit.
Thanks,
Ilmar
-
Case When nExpression1 & Power( 2, (nExpression2 + 1)) = 0 then 0 else 1 end -- returns 1 bit set
nExpression1 = nExpression1 | Power( 2, (nExpression2 + 1)) -- Sets bit
Check out BOL BitWise stuff...
also globally available functions (in Master DB)
Select fn_replinttobitstring(123)
Select fn_replbitstringtoint('00000000000000000000000001111011')
Hope that helps some.
Once you understand the BITs, all the pieces come together
-
Thanks!
Looks like this should get me started...
and what would emulate:
BITCLEAR(nExpression1, nExpression2)
Clears a specified bit (sets it to 0) in a numeric value and returns the resulting value.
- nExpression1
- Specifies the numeric value in which a bit is cleared.
- nExpression2
- Specifies the bit position in nExpression1 that is cleared. nExpression2 can range from 0 to 31; 0 is the rightmost bit.
Thanks for your help..
Ilmar
-
nExpression1 = nExpression1 ^ Power( 2, nExpression2) -- clear Bit nExpression2
(Bitwise Exclusive OR)
Once you understand the BITs, all the pieces come together -
Thanks again
Ilmar
-
Ilmar, a couple of things... You may want to make sure all the integers are BIGINTs for dealing with bits accessed with big numbers.
And check out SQLServer Central Article at http://www.sqlservercentral.com/columnists/dpoole/usingbitstostoredata.asp
and the thread at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=60&messageid=19585#bm93869
Once you understand the BITs, all the pieces come together -
Thanks a bunch for turning me on to those functions in master..
Here is what I came up with.. and they seem to work fine. (I'm just getting into SQL Server.. so if I'm doing something especially stupid or naive, please let me know (gently
)------------
CREATE FUNCTION dbo.BitClear (@nvalue BigInt, @nposition TinyInt)
RETURNS bit AS
BEGIN
Declare @test-2 char(32)
Declare @cresult char(32)
Declare @nResult BigInt
Set @test-2 = fn_replinttobitstring(@nvalue)
Set @cResult = Stuff(@test, 32-@nposition,1,'0')
set @nResult = fn_replbitstringtoint(@cResult)
Return @nResult
END
CREATE FUNCTION dbo.BitSet (@nvalue BigInt, @nposition TinyInt)
RETURNS bit AS
BEGIN
Declare @test-2 char(32)
Declare @cresult char(32)
Declare @nResult BigInt
Set @test-2 = fn_replinttobitstring(@nvalue)
Set @cResult = Stuff(@test, 32-@nposition,1,'1')
set @nResult = fn_replbitstringtoint(@cResult)
Return @nResult
END
CREATE FUNCTION dbo.BitTest (@nValue BigINT, @nPosition TinyInt )
RETURNS bit AS
BEGIN
Declare @test-2 char(32)
Declare @lReturn bit
Set @lReturn = 0
Set @test-2 = fn_replinttobitstring(@nValue)
Set @lReturn = substring(@test,32-@nPosition,1)
Return @lReturn
END
------------------
-
You are doing string manipulation to get your results. this is fine for readability, but if you need to call these functions for many records in a result set, it may be much faster to perform math operations within your UDFs. Also, I know from past experience, "inline" expressions perform much faster than UDF calls with the same operations, so eliminating calls to UDFs in queries where perfomance is an issue is best.
Also, you may want to indent and comment your UDF code so that a year from now you can easily see what the code is doing.
Once you understand the BITs, all the pieces come together -
Thanks..
string manipulation in VFP is blazingly fast.. I'll have to get used to it not being so in the SQL Server context..
and, yup, I do need to get around to comments, indents, and all that good stuff...
I appreciate the time you took to help out... now if I could only come up with something elegant for the IIF problem, my day would be made
-
I'm playing around with your math versions, and I'm not getting quite the result I expected. Did I misinterpret what you had in mind?
-----------
CREATE FUNCTION dbo.BitClear(@nvalue BigInt, @nposition TinyInt)
RETURNS BigInt AS
BEGIN
Declare @nResult Bigint
set @nResult = @nValue ^ Power( 2, @nPosition)
Return @nResult
END
CREATE FUNCTION dbo.BitSet(@nvalue BigInt, @nposition TinyInt)
RETURNS BigInt AS
BEGIN
Declare @nResult Bigint
set @nResult = @nValue | Power( 2, (@nPosition))
Return @nResult
END
-----------
now when I try to use them, I get the expected results for positions 0 and 1, but both functions give me identical results for positions 3 +
Declare @Cleared BigInt
Declare @Set BigInt
Declare @test-2 BigInt
Set @test-2 = 67
Set @cleared = dbo.BitClear(@test,3)
Set @set = dbo.BitSet(@test,3)
Select
@test-2,@cleared,@set,
fn_replinttobitstring(@test),
fn_replinttobitstring(@cleared),
fn_replinttobitstring(@set)
-
ok.. found the problem, here is a set of working functions:
-----------------
CREATE FUNCTION dbo.BitClear(@nvalue BigInt, @nposition TinyInt)
RETURNS BigInt AS
BEGIN
Declare @nResult Bigint
Declare @nResult2 BigInt
set @nResult = 0
set @nResult = @nValue ^ Power( 2, @nPosition)
Set @nResult2 = @nResult & @nValue
Return @nResult2
END
CREATE FUNCTION dbo.BitSet(@nvalue BigInt, @nposition TinyInt)
RETURNS BigInt AS
BEGIN
Declare @nResult Bigint
Set @nResult = 0
set @nResult = @nValue | Power( 2, (@nPosition))
Return @nResult
END
CREATE FUNCTION dbo.BitTest(@nValue BigInt, @nPosition TinyInt )
RETURNS bit AS
BEGIN
Declare @lReturn bit
set @lReturn = 0
Set @lReturn = Case
When @nValue & Power( 2, (@nPosition)) = 0
Then 0
Else 1
End
Return @lReturn
END
-
Only thing I might change is instead of
2
Use
Convert(BigInt, 2)
That will insure the POWER() result can be a BigInt. Perhaps even make the @nPosition a BigInt also ???. You might want to test with the higher bits to make sure.
Also, did you test performance comparing the String functions with the Math functions, compared to "inline"? If so, what did you observe?
Once you understand the BITs, all the pieces come together -
A couple of thoughts on this:
When using Power() with bigint, ALL parameters must be bigint, so as coded you will probably get arithmetic overflow errors when @nPosition is > 30.
Cast the 2 to bigint:
set @nResult = @nValue | Power( Cast(2 as bigint), (@nPosition))
Also, Exclusive OR doesn't clear a bit, it TOGGLES a bit:
1011
0100 ^
-------
1111 when you wanted 1011 (bit was already cleared).
To clear a bit, you need to AND all of the bits of @nValue with all ones, except for the position to be cleared, which should be zero. Say we have 11dec (1011 bin), and we want to clear bit 4 (leftmost here):
1011
0111 & (AND)
-------------
0011
You can accomplished this be declaring this at the top of you function:
declare @maxint bigint -- maximum for an unsigned 4-byte integer
set @maxint = Power( cast(2 as bigint), cast(32 as bigint)) - cast(1 as bigint)
then, to clear the bit, use:
-- clear Bit# nExpression2 in @n1
set @r = @n1 & ( @maxint ^ Power( cast(2 as bigint), @n2) )
Mike
-
Try these...
CREATE FUNCTION dbo.udf_bitTest (@prmValue bigint, @prmPosition tinyint)RETURNS bit ASBEGINDECLARE @TWO bigintSET @TWO = 2RETURN CASE WHEN @prmValue & POWER(@TWO, @prmPosition) = 0 THEN 0 ELSE 1 ENDENDgoCREATE FUNCTION dbo.udf_bitClear(@prmValue bigint, @prmPosition tinyint)RETURNS bigint ASBEGINDECLARE @TWO bigintSET @TWO = 2RETURN @prmValue ^ ((@prmValue & POWER(@TWO, @prmPosition)) & POWER(@TWO, @prmPosition))ENDgoCREATE FUNCTION dbo.udf_bitSet(@prmValue bigint, @prmPosition tinyint)RETURNS bigint ASBEGINDECLARE @TWO bigintSET @TWO = 2RETURN @prmValue | POWER(@TWO, (@prmPosition))ENDgoHTH,Art -
Thanks guys..
Looks like Art's code incorporates the various suggestions and the corrections to the setclear..
Seems to work fine up through position 30... which is all I need..
I appreciate all the help I've gotten on this forum
Ilmar
p.s. no, I haven't had a chance to test performance of these vs the stringmanipulation ones
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply