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 20 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy