November 6, 2007 at 9:07 am
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
November 6, 2007 at 9:35 am
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.
November 6, 2007 at 9:55 am
Thanks - but I'm only wanting certain bits - see original post...
In this case the answer I'm needing is decimal 10
November 6, 2007 at 10:28 am
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.
November 6, 2007 at 10:30 am
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?
November 6, 2007 at 10:55 am
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 ?
November 6, 2007 at 11:00 am
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?
November 6, 2007 at 11:22 am
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 ALL
SELECT 0x006A0000 UNION ALL
SELECT 0x00270000 UNION ALL
SELECT 0x00370000 UNION ALL
SELECT 0x00470000 UNION ALL
SELECT 0xFF28FFFF
SELECT 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 DecimalValue
FROM @BinaryTest
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2007 at 11:22 am
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'.
November 6, 2007 at 11:34 am
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.
November 6, 2007 at 11:42 am
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
November 6, 2007 at 12:09 pm
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 🙂
November 6, 2007 at 12:24 pm
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
Change is inevitable... Change for the better is not.
November 6, 2007 at 1:41 pm
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!!!
November 7, 2007 at 2:29 am
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 19 total)
You must be logged in to reply to this topic. Login to reply