Numeric To Binary Conversion

[clegg]

Hi all!

I've got a field in a SQL 2000 db that holds an integer value that is set from a VB front-end (a series of 11 checkboxes) so that the number is a binary representation of whether the boxes are checked or not.

For example 11111111111 = all checked and a value in the DB of 8191 (decimal value of the binary).

My problem is that I have to interogate this decimal value from a SQL Stored Procedure to find out if 1 particular option is set (which affects an update statement).

Turning it back into binary in VB would be easy (and then check the appropriate bit) but how do I do this from within SQL??

Any help would be appreciated
Clegg

 

[vongrunt]

> My problem is that I have to interogate this decimal value from a SQL Stored Procedure to find out if 1 particular option is set (which affects an update statement).

Pass 8191 (integer) to stored proc, then use & operator to check whether particular bit is set or not.


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[NoCoolHandle]

Just incase it comes in handy.. (an alternate solution - and probably not as good - just kind of usefull for int to binary conversions)

Create Proc IntegerToBinary
@int int,
@out varchar(8000) output
as
set nocount on
declare @s varchar(8000)
declare @n int
set @n = @int
set @s = rtrim(ltrim(cast(@n % 2 as varchar(8000))))
set @n = @n / 2
while @n <> 0
	begin
		set @s =  rtrim(ltrim( cast(@n % 2 as varchar(8000)))) + @s
		set @n = @n / 2
	end
Set @out = @s
go
declare @x varchar(8000)
exec integertobinary 11,@x output
select @x

Hope that helps


Rob

 

[vongrunt]

How about simple

select fn_replinttobitstring(11)

?


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]