Help with extracting Bits out of Binary Number
-
I have a Hex number 0x00280000 which in binary is
0000 0000 0010 1000 0000 0000 0000 0000
Starting from the left...I need to convert the bits 01010 (position 10-14) into Decimal - in this case the result would be 10 (2+8)
I'm doing (trying) this in a sql procedure in sql server 2005.
Appreciate any help - Thanks
Jim
-
HEX is the same as varbinary, in SQL you can load the value like so and convert to int.
declare @x as varbinary(100)
set @x = 0x00280000
select cast(@x as int)
-- demonstartes you get the same answer from the number.
select cast(2621440 as varbinary(100))
So unless you are reordering the bits this should work.
-
Thanks - but I'm only wanting certain bits - see original post...
In this case the answer I'm needing is decimal 10
-
I am trying to figure out then how you make your conversion to binary (from HEX) and determine your starting position. Can you explain your logic a bit more and use more than one example. It just isn't jumping out at me.
-
I'm sure someone is going to trot out a tally table solution any minute now. That being said - doing it the old fashioned way might work....
alter function bintoDec(@bin as varchar(100))
Returns int
as
begin
declare @idx int
declare @result int
set @result=0
set @idx=1
While @idx<=len(rtrim(@bin))
begin
select @Result=@result*2+cast(substring(@bin,@idx,1) as int),@idx=@idx+1
end
return @result
end
usage would look like (in your case)
select dbo.bintodec(substring('00000000001010000000000000000000',10,5))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Sure...Basically I have a large Decimal number ie. 168034788 which I then have to convert to Hex ie. 0A0401E4.....This represents a collection of data which has to be looked at from a binary point of view...from right to left - first 5 bits equal a, next 13 bits equal b, next 5 bits equal c, next 5 bits equal d and toss the leftmost 4 bits (total 32 bits). Each of these entities needs to be converted back into a decimal number (and they don't even line up on the byte boundry)...
I know, I know - I didn't design it, I just have to deal with it
BTW these values are dynamic so I really can't use the function with a hardcoded binary string...
Clear as mud ?
-
Sounds like the time I had to try to translate some mainframe data.
Now
from right to left - first 5 bits equal a, next 13 bits equal b, next 5 bits equal c, next 5 bits equal d and toss the leftmost 4 bits (total 32 bits).
How do I know 5, 13, 5, 5? Are those widths hard values or does it vary and there is something that triggers what is what?
-
First of all, you need to count the bits from the right... not the left... and they should be counted as powers of 2 starting with the exponent 0... then you can do fun stuff like the following without a tally table, etc. I'm not sure what you mean by the groups of 5, 13, etc... maybe I can work on that a bit, later, if you'd care to explain just a wee "bit" more :P... Do you expect the right most bit of each of those groups to represent 20 in your returned decimal number?
DECLARE @BinaryTest TABLE (X VARBINARY(10))INSERT INTO @BinaryTest (X)SELECT 0x00280000 UNION ALLSELECT 0x006A0000 UNION ALLSELECT 0x00270000 UNION ALLSELECT 0x00370000 UNION ALLSELECT 0x00470000 UNION ALLSELECT 0xFF28FFFFSELECT X,SIGN(CAST(X AS INT) & (POWER(2,19))) AS Bit19,SIGN(CAST(X AS INT) & (POWER(2,21))) AS Bit21,CAST(X AS INT) & (POWER(2,19)+POWER(2,21)) AS DecimalValueFROM @BinaryTest--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think the first step we have to address here is converting to BITS (one-byte patterns for each HEX value) so I have this
CREATE FUNCTION HEXTOBIN (@valIn varbinary(100))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @valInPos int
DECLARE @strRet varchar(8000)
SET @valInPos = 1
WHILE @valInPos <= len(@valIn)
BEGIN
SET @strRet = IsNull(@strRet,'') + (SELECT
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 128 = 128 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 64 = 64 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 32 = 32 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 16 = 16 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 8 = 8 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 4 = 4 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 2 = 2 THEN '1' ELSE '0' END +
CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 1 = 1 THEN '1' ELSE '0' END)
SET @valInPos = @valInPos + 1
END
RETURN(@strRet)
END
GO
which you use like this
SELECT dbo.HEXTOBIN(0x00280000)
and handles the first step of getting it to 0's and 1'.
-
OK combine my HEXTOBIN with Matts BINTODEC functions and you get this as long as you are working with fixed start and stop positions.
SELECT
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),29,4)) a,
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),16,13)) b,
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),11,5)) c,
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),5,5)) d
And if you are coming from the decimal change
0x0A0401E4
to
cast(168034788 as varbinary(100))
and you should be good to go.
-
Sorry found a typo this is the corrected version
SELECT
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),28,5)) a,
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),15,13)) b,
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),10,5)) c,
dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),5,5)) d
-
Thanks for the replies...
Yes the widths are fixed ie . always the first 5 from left, then 13 etc. etc.
I'll play with some of the suggestions and see how far I get....other work getting in the way right now
-
You didn't answer my question, though... Do you expect the right most bit of each of those groups to represent 20 in your returned decimal number?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The rightmost bit (if set) of each "group" would be 1...ie 8421
^
Looks like between the 2 functions, I've got it working - I'll test with some more data - after I do it by hand
Thanks again for all the help!!!
-
DECLARE@HEX BINARY(4)
SET@HEX = 0x00280000
SELECT(CAST(@HEX AS BIGINT) / POWER(2, 18)) & 0x1f
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply