extracting binary componenents 1

[vadimg]

is there a command that will extract the binary components from a number?

example 1
in the database: 0000 0111 (7)
i need this command to output : 1,2,4

in the database: 0010 1010 (44)
i need this command to output : 4,8,32

 

[mrdenny]

You should be able to do this in a function fairly easily.

create function fn_ConvertBin
   @EnteredData = varchar(8)
as
returns varchar(25)
declare @ReturnData varchar(25)
set @ReturnData = ''
if SubString(@EnteredData, 8,1) = 1
   set @ReturnData = @ReturnData + '1,'
if SubString(@EnteredData,7,1) = 1
   set @ReturnData = @ReturnData + '2,'
if SubString(@EnteredData,6,1) = 1
   set @ReturnData = @ReturnData + '4,'
if SubString(@EnteredData,5,1) = 1
   set @ReturnData = @ReturnData + '5,'
...
If @ReturnData <> '' /*Trim the last comma as needed*/
  set @ReturnData = left(@ReturnData, len(@ReturnData)-1)
return @ReturnData

BTW 44 = 00101100
00101010 = 42

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vadimg]

BTW 44 = 00101100
00101010 = 42

all right there, smartass!! ;0) i was typing too fast ;0)
a star for your solution.

Howeverm I actually wanted to avoid using a user function. Doesn't sql server have anything native for this?

 

[mrdenny]

Not that I know of. If the data was already in the numeric version you could use the bitwise operators to do some figuring out about it. See "bitwise operators" in BOL for those.

This is the kind of thing that SQL Server would want you to do on the client side, not the server side.

Yeah, I'm a smartass. I'm a slickler for accurecy (something like that). Not with my spelling, but with everything else.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dbomrrsm]

Out of curiosity I took this a bit further:

create the original function

create function fn_ConvertBin
   (@EnteredData varchar(8))
returns varchar(25)
as
Begin
declare @ReturnData varchar(25)
set @ReturnData = ''
if SubString(@EnteredData, 8,1) = 1
   set @ReturnData = @ReturnData + '1,'
if SubString(@EnteredData, 8,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData,7,1) = 1
   set @ReturnData = @ReturnData + '2,'
if SubString(@EnteredData,7,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData,6,1) = 1
   set @ReturnData = @ReturnData + '4,'
if SubString(@EnteredData,6,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData,5,1) = 1
   set @ReturnData = @ReturnData + '8,'
if SubString(@EnteredData,5,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData, 4,1) = 1
   set @ReturnData = @ReturnData + '16,'
if SubString(@EnteredData, 4,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData,3,1) = 1
   set @ReturnData = @ReturnData + '32,'
if SubString(@EnteredData,3,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData,2,1) = 1
   set @ReturnData = @ReturnData + '64,'
if SubString(@EnteredData,2,1) = 0
   set @ReturnData = @ReturnData + '0,'
if SubString(@EnteredData,1,1) = 1
   set @ReturnData = @ReturnData + '128,'
if SubString(@EnteredData,1,1) = 0
   set @ReturnData = @ReturnData + '0,'
If @ReturnData <> '' /*Trim the last comma as needed
 - comma needed in this case*/
set @ReturnData = @ReturnData
Return @ReturnData
End

Then create a procedure:

CREATE PROC BIN_TO_NUMBER (@BIN_INPUT varchar (8))
AS
DECLARE @s varchar(100)
DECLARE @table table (ID varchar(20)) 

SET @s = (select dbo.fn_ConvertBin(@BIN_INPUT))

WHILE (@s != '')


    BEGIN
    	INSERT @table
    	SELECT SUBSTRING(@s,1,PATINDEX('%,%',@s) - 1)
    	SELECT @s = RIGHT(@s,LEN(@s) - PATINDEX('%,%',@s))
END

SELECT sum(cast(ID as int)) FROM @table

So:

EXEC BIN_TO_NUMBER '11111111'

Gives the answer 255.

I sometimes think I have nothing better to do !

[blue]DBomrrsm[/blue]