VarBinary(max) to list of integers

Question

For my application I dump some values into a column of varbinary(max). Now I'm trying to fetch the data from there again an would like to get a list from sql back with all values.

Within my C# application I perform the following task:

Data = ints.SelectMany(BitConverter.GetBytes).ToArray();

This Data is then inserted into the database.

What I'm looking for is a SQL script on how to retrieve the data, not simply as a blob, but as a list of all values. So something like this:

SELECT * FROM RawData CROSS APPLY CastToInt(SplitBinary(RawData.Data, 4)) WHERE RawDataId = 1;

Anyone know of a way to implement this kind of feature?

As background info, I'm trying to create a graph of a RawData within Grafana.

score 2 · Answer 1 · answered Nov 25 '21 at 14:12

One method to split the 32-bit int values is with a binary string split function. The TVF example below uses a utility tally table to facilitate the task.

Tally table DDL:

DROP TABLE IF EXISTS dbo.Tally;
CREATE TABLE dbo.Tally(Number int NOT NULL);
--load 1 million numbers
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS t10a CROSS JOIN t10 AS t10b CROSS JOIN t10 AS t10c)
    ,t1m AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS Number FROM t1k AS t1ka CROSS JOIN t1k AS t1kb)
INSERT INTO dbo.Tally WITH(TABLOCKX) (Number) 
SELECT Number
FROM t1m;
--create primary key on number
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally PRIMARY KEY (Number)
    WITH(FILLFACTOR=100, MAXDOP = 1);
GO

Binary int split TVF:

CREATE OR ALTER FUNCTION dbo.BINARY_INT_STRING_SPLIT(@ints varbinary(MAX))
RETURNS TABLE
AS
RETURN(
    --reverse serialized bytes for little-endian order
    SELECT CAST(SUBSTRING(@ints,Tally.Number*4,1) + SUBSTRING(@ints,Tally.Number*4-1,1) + SUBSTRING(@ints,Tally.Number*4-2,1) + SUBSTRING(@ints,Tally.Number*4-3,1) AS int) AS int
    FROM dbo.Tally
    WHERE Tally.Number <= DATALENGTH(@ints)/4
);
GO

Example usage:

CREATE TABLE dbo.RawData(RawDataId int, Data varbinary(MAX));
INSERT INTO dbo.RawData(RawDataId, Data) VALUES(1, 0x01000000);
INSERT INTO dbo.RawData(RawDataId, Data) VALUES(2, 0x02000000);
INSERT INTO dbo.RawData(RawDataId, Data) VALUES(3, 0xffffffff);
GO

SELECT RawDataId, ints.int
FROM dbo.RawData
CROSS APPLY dbo.BINARY_INT_STRING_SPLIT(RawData.Data) AS ints;
GO

This looks very promising! Could this also be rewritten to work with reals? — Foitn, Nov 26 '21 at 07:47
@Foitn, T-SQL doesn't allow an conversion from varbinary to real/float. Even if it did, there may be additional complexities with the differing .NET single data type and SQL Server float data type structures and endianness. A SQLCLR function would be a better fit a real conversion, IMHO. — Dan Guzman, Nov 26 '21 at 11:07

VarBinary(max) to list of integers

1 Answers1