convert string to numerical data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

convert string to numerical data

Author

Topic

kevincallan
Starting Member

13 Posts

Posted - 2009-03-02 : 14:15:42

I have an ~8K-long-string that starts with:

0000 01F6 408A E6EA 1999 DDE4 408A F4AB F219 CA67 408B 026D D68F 27CC 408B 102F C6CE 8A31 408B 1DF1 C2AC 85B5 408B 2BB3 C9FD AE7A 408B 3975 DC96 989C 408B 4737 FA4B D83D 408B 54FA 22F2 017C 408B 62BC 565D A879 408B 707E 9463 6151 408B 7E40 DCD7 C027 408B 8C03 2F8F 5918 408B 99C5 8C5E C044 408B A787 F31A 89CA 408B B54A 6397 49CB 408B C30C DDA9 9465 408B D0CF 6125 FDB9 408B DE91 EDE1 19E5 408B EC54 83AF 7D09 408B FA17 2265 BB46 408C 07D9 C9D8 68B9 408C 159C 79DC 1982 ...

Many of these chars don't display nicely in a query since they are non-printable chars like the first two nulls.

I do, however, know the data structure that this string represents:

STRUCTURE
LENGTH: INT
DATA(): FLOAT

What I want to do is select the first four bytes, convert them to an INT, and use them to know how many floats there are in the array. Once that is known, I want to convert the 8-byte long floats into the values and return them as a recordset.

I've experimented with taking substring slices of this string and trying to use the CAST and CONVERT functions to provide the INT value but get errors on every attempt.

A typical attempt and response looks like this:

select cast(substring(value3,1,4) as int) from testdetails
where testsummaryid = 830

with a SQL Server response of:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '..ö' to data type int.

Any suggestions on the correct way to process this information?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-03 : 03:39:39

have to be honest, i'm not entirely sure what you're after???
...but look at ASCII() and UNICODE() in Books Online, as i think that's what you're looking for

Em

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-03-03 : 11:49:00

Your main problem is that you cannot convert a binary(8) that contains the IEEE754 representation of a double-precision number into a SQL Server float just by calling CAST / CONVERT ("Explicit conversion from data type binary to float is not allowed.")

Here, I've written a function that essentially reparses the binary representation of the number. This is probably insane. It would almost certainly make more sense to try to interpret the binary(8) bit pattern as a float outside SQL Server, possibly as a CLR user-defined function.


CREATE FUNCTION dbo.HexStringToBinary(@hs varchar(max))
RETURNS varbinary(max)
AS
BEGIN
  IF SUBSTRING(@hs, 1, 2) = '0x' SET @hs = SUBSTRING(@hs, 3, LEN(@hs))
  RETURN CASE WHEN @hs LIKE '%[^0-9A-Fa-f]%' THEN NULL
    ELSE (SELECT CAST('' as xml).value('xs:hexBinary(sql:variable("@hs"))', 'varbinary(max)')) END
END
GO

-- This function is for demonstration purposes and has not been tested:
-- Do not use it for anything that matters!
CREATE FUNCTION dbo.BinaryToFloat(@float_bytes binary(8))
RETURNS float
AS
BEGIN
  DECLARE @sign float, @raw_exponent smallint, @raw_mantissa bigint, @true_exponent smallint
  SET @sign = CASE WHEN CAST(SUBSTRING(@float_bytes, 1, 1) AS tinyint) & 0x80 = 0x80 THEN -1.0 ELSE 1.0 END
  SET @raw_exponent = (CAST(SUBSTRING(@float_bytes, 1, 2) AS smallint) & 0x7FF0) / 0x10
  SET @raw_mantissa = CAST(SUBSTRING(@float_bytes, 2, 7) AS bigint) & 0xFFFFFFFFFFFFF
  -- Put some Zero, denormal, infinity and NaN handling here!
  SET @true_exponent = CASE WHEN @raw_exponent BETWEEN 0x001 AND 0x7FE THEN @raw_exponent - 0x3FF ELSE NULL END
  RETURN @sign * CAST((@raw_mantissa | 0x10000000000000) AS float) * POWER(CAST(2.0 AS float), @true_exponent - 52)
END
GO

DECLARE @mystery_string varchar(max), @mb varbinary(max)
SET @mystery_string = '0000 01F6
408A E6EA 1999 DDE4 408A F4AB F219 CA67
408B 026D D68F 27CC 408B 102F C6CE 8A31
408B 1DF1 C2AC 85B5 408B 2BB3 C9FD AE7A
408B 3975 DC96 989C 408B 4737 FA4B D83D
408B 54FA 22F2 017C 408B 62BC 565D A879
408B 707E 9463 6151 408B 7E40 DCD7 C027
408B 8C03 2F8F 5918 408B 99C5 8C5E C044
408B A787 F31A 89CA 408B B54A 6397 49CB
408B C30C DDA9 9465 408B D0CF 6125 FDB9
408B DE91 EDE1 19E5 408B EC54 83AF 7D09
408B FA17 2265 BB46 408C 07D9 C9D8 68B9
408C 159C 79DC 1982'
SET @mb = dbo.HexStringToBinary(
  REPLACE(REPLACE(REPLACE(@mystery_string, ' ', ''), CHAR(13), ''), CHAR(10), ''))

SELECT n, float_bytes, dbo.BinaryToFloat(float_bytes) AS f
FROM (
    SELECT N.n, SUBSTRING(@mb, 5 + 8*N.n - 8, 8) AS float_bytes
    FROM dbo.Numbers AS N
    WHERE N.n BETWEEN 1 AND CAST(SUBSTRING(@mb, 1, 4) AS int)
  ) AS A

kevincallan
Starting Member

13 Posts

Posted - 2009-03-03 : 15:09:21

Thank you both for your consideration and help. I am very appreciative. I will use this information to try to arrive at a solution. Will post my solution when I get it.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources