SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split Value and Convert Hex to Decimal


Split Value and Convert Hex to Decimal

Author
Message
agbondoc
agbondoc
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 50
Hi everyone,

I have been task to decode a very long Hex string to decimal for further analysis. The output I'm told should be two (2) columns between 2,000 to 3,000 rows of decimals values.

My issues are the following...

1. I don't know what the split value is
2. I am told the values are 32 bit floating data array but I don't think it is.
3. The hex are supposedly 4 characters to it decimal value.
for example: 1001: 5.741E-42
0100: 3.59E-43
CD44: 7.3635E-41
4. Number 3 would only make sense if the values i'm told is 16 bit float and not 32.

I apologize in advance if this seemed confusing.

Thanks.
Hunterwood
Hunterwood
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 297
agbondoc (8/19/2014)
Hi everyone,

I have been task to decode a very long Hex string to decimal for further analysis. The output I'm told should be two (2) columns between 2,000 to 3,000 rows of decimals values.

My issues are the following...

1. I don't know what the split value is
2. I am told the values are 32 bit floating data array but I don't think it is.
3. The hex are supposedly 4 characters to it decimal value.
for example: 1001: 5.741E-42
0100: 3.59E-43
CD44: 7.3635E-41
4. Number 3 would only make sense if the values i'm told is 16 bit float and not 32.

I apologize in advance if this seemed confusing.

Thanks.


Your description is very vauge and it is hard to understand what you are after.
First: All hex numbers in your example are two bytes (it takes 2 hex digits to represent one byte).
So before you can go on, you need to know exactly how the data is formatted; is it decimal/float/... , 16 bit/32 bit/...

Since you are asking your question in this forum, I assume you are looking for a SQL solution. Based on the information in your query I just have to guess what you are after, but I will give a short sample, that might give you some direction on how the problem can be solved.

In this short example I take data stored as a SQL "binary" and spilt it in 4-byte chunks, which are then converted into float:

-- CREATE A FUNCTION TO CONVERT BINARY TO FLOAT
-- (code from: http://ask.sqlservercentral.com/questions/43447/how-do-i-convert-a-hex-to-a-floating-point-number.html)
CREATE FUNCTION dbo.fnBinaryReal2Real
(
@BinaryFloat BINARY(4)
) RETURNS REAL
AS

BEGIN

DECLARE @Mantissa REAL,
@Exponent SMALLINT,
@IntValue INT,
@Real2 REAL

SELECT
@Real2 = CAST(2.0 AS REAL),
@IntValue = CAST(@BinaryFloat AS INT),
@Exponent = (@IntValue & 0x7f800000) / 0x00800000,
@Mantissa = 1.0 + (@IntValue & 0x007FFFFF) * POWER(@Real2, -23)

RETURN SIGN(@IntValue) * @Mantissa * POWER(@Real2, @Exponent - 127)

END
GO

-- CREATE A TALLY TABLE
-- (code from: http://www.sqlservercentral.com/articles/T-SQL/62867/)

IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2


-- GET THE BINARY DATA
declare @data binary(12)
set @data = cast(0x40490FDB3FB504F342280000 as binary(12))

-- "LOOP" OVER THE BINARY STRING USING A TALLY TABLE
select dbo.fnBinaryReal2Real(substring(@data, (n-1) * 4 + 1, 4))
from Tally
where n <= len(@data) / 4



Your question indicates that your data might be represented as a string. If that is the case, you can have a look att this page to see how to convert that into binary:
http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx


Good luck!

/M
agbondoc
agbondoc
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 50
Thanks for for responding to my query.

I completely understand where you are coming from. My issue is I don't have a lot of information and I have been asked to solve and convert this "mystery" hex string.

The other issue is proprietary. I get no cooperation from the people that built the db (sql) and so doing a reverse engineering is a little more challenging.

With that said, I think your solution might work.

Thanks.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15688 Visits: 18626
agbondoc (8/19/2014)
Hi everyone,

I have been task to decode a very long Hex string to decimal for further analysis. The output I'm told should be two (2) columns between 2,000 to 3,000 rows of decimals values.

My issues are the following...

1. I don't know what the split value is
2. I am told the values are 32 bit floating data array but I don't think it is.
3. The hex are supposedly 4 characters to it decimal value.
for example: 1001: 5.741E-42
0100: 3.59E-43
CD44: 7.3635E-41
4. Number 3 would only make sense if the values i'm told is 16 bit float and not 32.

I apologize in advance if this seemed confusing.

Thanks.


Quick question, do you have any means of asserting the correctness of the output?
Cool
agbondoc
agbondoc
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 50
Hi Eirikur,

I do, actually. There is a UI that gives me the data in decimal as an output, e.g. 1.234.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search