Problem with Varbinary

Question

Problem with Varbinary

juergen.vogelauer

Valued Member

Points: 60
More actions
May 28, 2014 at 12:57 am

#286965

Hello Guys,
I have a problem with varbinary. By the way I am a real noob concerning SQL 🙂
In a table named "AnalogValue" there is a field "Value" which is a varbinary(max) datatype.
I know that in Byte 10-13 are the values I need (float values).
With this select I get Byte 10-13 ->
select top 1 SUBSTRING(Value, 10, 4) as Wert
from AnalogValue
where analogchannelref IN ('1090','1102')
order by startTime DESC
Now I want to CAST or CONVERT the result, so that I get the real float values.
This is where I have no clue.
I tried to work with convert, but it is not possible to convert a varbinary(max) into a float.
select top 1 CONVERT(Float,SUBSTRING(Value, 10, 4)) as Wert
from AnalogValue
where analogchannelref IN ('1090','1102')
order by startTime DESC
Can you help me out?
Thanks a lot!
Jürgen

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 1

There are a couple of ways that numbers may have been stored in a VARBINARY. One way is a direct conversion of the numeric value to binary represented by hex digits. Another way is that someone may have first converted the number to a string and then saved the string which, of course, will be totally different than saving the numeric value directly.

If you could, please run the following code and provide the output so that we can help better.

SELECT TOP 10

Wert = SUBSTRING(Value, 1, 20)

FROM dbo.AnalogValue

WHERE AnalogChannelRef IN ('1090','1102')

;

Also, since you're a "noob", I recommend that you get into the habit of always using the 2 part naming convention for all tables, views, and other objects in SQL Server. It'll help a lot if you ever work on a system that uses differenct schema names and it'll also help a bit for performance especially on high-hit-ratio GUI related code.

--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)

juergen.vogelauer Valued Member Points: 60 More actions · Answer 2

Hi Jeff,

Thank you for your response!

If I run your code, I get this result:

0x01001E527B932ED148000000C842020000D088C3

0x0100808D22A92ED14800DE364742000000D088C3

0x0100DA96746B2FD14800EF8F7242000000D088C3

0x010034A0C62D30D1480089E15642000000D088C3

0x010040B3EDD730D14800E1597A42000000D088C3

0x0100A613C6DE30D1480090EF7C42000000D088C3

0x010072D476EC30D14800038C8942000000D088C3

0x0100A6B6A7F030D14800E1B58642000000D088C3

0x0100DA98D8F430D14800DE518442000000D088C3

0x01000E7B09F930D14800F80D8642000000D088C3

Also thank you for your recommendation concerning naming convention, I appreciate it!

Regards

Jürgen

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

Quick question, is it an IEEE 754 single precision float, and is it signed or unsigned?

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SQL Server has no explicit conversion from VARBINARY to FLOAT, but you can roll your own. Refer to this thread.

This inline table-valued function is based on the UDF described in the link. Note that implicit conversions in the original code have been replaced with explicit conversions.

-- Create a table-valued function

CREATE FUNCTION [dbo].[if_VarbinaryFloat4ToFloat4]

(@BinaryFloat AS BINARY(4))

RETURNS TABLE AS

RETURN

SELECT Float4Byte = CONVERT(REAL(24),

CONVERT(FLOAT(53),sign(CONVERT(INT,@BinaryFloat,0)),0)

*((1.000000000000000e+000)+CONVERT(FLOAT(53),CONVERT(INT,@BinaryFloat,0)&(8388607),0)*(1.192092895507813e-007))

*power((2.000000000000000e+000),CONVERT(FLOAT(53),(CONVERT(INT,@BinaryFloat,0)&(2139095040))/(8388608)-(127),0)),0)

Usage, compared to the UDF:

SELECT *

INTO #Temp

FROM (

SELECT [Value] = 0x01001E527B932ED148000000C842020000D088C3 UNION ALL -- varbinary(20)

SELECT 0x0100808D22A92ED14800DE364742000000D088C3 UNION ALL

SELECT 0x0100DA96746B2FD14800EF8F7242000000D088C3 UNION ALL

SELECT 0x010034A0C62D30D1480089E15642000000D088C3 UNION ALL

SELECT 0x010040B3EDD730D14800E1597A42000000D088C3 UNION ALL

SELECT 0x0100A613C6DE30D1480090EF7C42000000D088C3 UNION ALL

SELECT 0x010072D476EC30D14800038C8942000000D088C3 UNION ALL

SELECT 0x0100A6B6A7F030D14800E1B58642000000D088C3 UNION ALL

SELECT 0x0100DA98D8F430D14800DE518442000000D088C3 UNION ALL

SELECT 0x01000E7B09F930D14800F80D8642000000D088C3

) d

SELECT

t.[Value],

[Wert] = SUBSTRING(Value, 10, 4),

f.*,

[UDF result] = dbo.fnBinaryReal2Real (SUBSTRING(Value, 10, 4))

FROM #Temp t

CROSS APPLY dbo.if_VarbinaryFloat4ToFloat4 (SUBSTRING(t.[Value], 10, 4)) f

DROP TABLE #Temp

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

juergen.vogelauer Valued Member Points: 60 More actions · Answer 5

juergen.vogelauer

Valued Member

Points: 60

May 29, 2014 at 12:01 pm

#1717486

I think its a single float, signed.

Thx

juergen.vogelauer Valued Member Points: 60 More actions · Answer 6

Hi Chris,

Thank you for your answer!

Today we have public holiday here in my country. I will try your solution next week.

Have a nice day!

Jürgen