Interrogate bits in a binary string

[KMarshall]

I need to interrogate a particular bit in a binary string.

If I take the number 56246 and convert it to binary using the windows calculator I end up with this binary string 1101101110110110

I am interested in determining the status of the bold bit.

If I take the number 56246 that is stored in my database and CONVERT it to binary, I seem to get a HEX value.

DELCARE @i int, @b binary(16)
SET @i = 56246
SET @b = CONVERT(binary(16), @i)
PRINT @b

I have no idea how to decide if the bit I want to check is a 1 or 0.

Any help would be most appreciated!

 

[stevexff]

You need a bitwise AND operation (&).

DECLARE @i int, @j int, @testbits int
SET @testbits = 64
SET @j = @i & @testbits
IF @j = @testbits
   BEGIN 
      ...
      ...
   END

This tests the bold bit in your example by ANDing

@i        11011011 10110110 with
@testbits 00000000 01000000 to give
@j        00000000 00000000

If you need to check if 3 bits are on, use SET @testbits = 64 + 16 + 1. But this can get tricky if some are on and some are off. You are probably better off doing them individually. A useful trick borrowed from assembler (where you tend to have to do a lot of this) is to create a set of variables with meaningful names for the bit values like

SET @active = 64
SET @disabled = 32

then you can use the variables as symbolic values in your code.

 

[dbomrrsm]

CREATE  function DECTOBIN(@n int ) 
returns varchar(255)
as
BEGIN
DECLARE @i int,@temp int, @s varchar(255)
SET @i=@n
SET @s=''
WHILE (@i>0)
BEGIN
SET @temp=@i % 2
SET @i=@i /2
SET @s=char(48+@temp)+@s
END
RETURN @s
END

Then

select dbo.dectobin(56246)

to do the check you could use

select case
WHEN Substring(reverse(dbo.dectobin(56246)),7,1) = 0 then 'Off'
Else 'On'
End

If the digit you want to check is always seven from the right.

[blue]DBomrrsm[/blue]

 

[KMarshall]

Fantastic, thanks for your help.

 

[dbomrrsm]

Your welcome.

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

You can use the follwing to convert to 1101101110110110 back to 56246

CREATE  function BINTODEC(@s VARCHAR(255) ) 
--Converts a binary number to decimal.
returns int
as
BEGIN
DECLARE @i int, @temp char(1), @result int
SELECT @i=1
SELECT @result=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
SELECT @result=@result+ (ASCII(@temp)-48)*POWER(2,LEN(@s)-@i)
SELECT @i=@i+1
END
return @result
END

select dbo.bintodec('1101101110110110')

[blue]DBomrrsm[/blue]

 

[stevexff]

Sorry, from your first post I got the impression that your data was already an int. What column type are you using to store the binary data? Is the part you want always at the same offset?

 

[KMarshall]

Yes, the data was already an int.
Basically what I'm doing is pulling back a set of data and deciding if a particular bit has been turned on or off. It is always the same bit that I am checking.

Both suggestions have been helpful.

My problem now is that other events make an entry in the table that I am pulling the data from which means that I could get the following results:

Off,Off,On,On,On,and so on (ie false readings),
instead of the desired On, Off, On, Off, On sequence.

Looks like I need to create a temporary table and loop throught the rs whilst populating the temp table only when the value has changed from the previous value.

Oh well.....

 

[stevexff]

Assuming the table has an int column that really holds 16 flag bits, how hard would it be to add a bit column(s) to the table? You could use a trigger to do the hard work of extracting the bit setting you need from the int column whenever the table was inserted or updated, and use it to populate the boolean column.

Thereafter it would be self-maintaining, and you could use the bit column easily in a SELECT without having to decode the bits each time.

 

[donutman]

I don't understand why you aren't happy with stevexff's 1st post. What do you mean by:

Off,Off,On,On,On,and so on (ie false readings),
instead of the desired On, Off, On, Off, On sequence.

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]