How would you... (binary info in a table)

I have run into a challenge that I'm not sure how to best solve. I'd
appreciate any opinions or input.

I am working with a third party database. They are storing some data
that I need to use in a binary field. I've got the code to parse the
binary and reconstruct what I need. Unfortunately, there might be
multiple "entries" stored in a single binary field. a certain byte,
let's just say the first, will always be the count of "entries" in
this particular SQL entry.
An example:
then entry might be:
0x01000012341234
where 12341234 is the data entry that I will parse.
Another possible entry is:
0x03000012341234567856789ABC9ABC
The first byte indicates that there are three data values I want to
parse out:
12341234
56785678
9ABC9ABC
The portions of the binary I need are always the same length and there
may be from 1 to ~100 of them. (usually 1 if it matters.)

The big question:
How could a SQL query return an entry for each of the "entries" in the
binary field? For the second example I would want three entries in my
results, each row returning a different section of the binary data.
For the first, only one row. I'll be querying the set and expecting to
get back more results than the number of entries in the set.

I haven't really tried much with this yet, (other than some mental
calisthenics were I fell down onto the mat pretty hard), so just some
direction on where to begin would be helpful.


(Yes, I understand that the difficulty is that this data shouldn't be
stored this way, but there is not much I can do about that.)

Thanks...
James Fraser
jbf1@concentric.net

Re: How would you... (binary info in a table)

James Fraser (jbf1@concentric.net) writes:
> I am working with a third party database. They are storing some data
> that I need to use in a binary field. I've got the code to parse the
> binary and reconstruct what I need. Unfortunately, there might be
> multiple "entries" stored in a single binary field. a certain byte,
> let's just say the first, will always be the count of "entries" in
> this particular SQL entry.
> An example:
> then entry might be:
> 0x01000012341234
> where 12341234 is the data entry that I will parse.
> Another possible entry is:
> 0x03000012341234567856789ABC9ABC
> The first byte indicates that there are three data values I want to
> parse out:
> 12341234
> 56785678
> 9ABC9ABC
> The portions of the binary I need are always the same length and there
> may be from 1 to ~100 of them. (usually 1 if it matters.)
>
> The big question:
> How could a SQL query return an entry for each of the "entries" in the
> binary field? For the second example I would want three entries in my
> results, each row returning a different section of the binary data.
> For the first, only one row. I'll be querying the set and expecting to
> get back more results than the number of entries in the set.

Have a look at my web site, at
http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-lengt h.
There is an example with binary values further down.

In order to apply the technique on a table column see the section
http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists.

There is an SQL 2000 version of the article as well.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How would you... (binary info in a table)

On Sep 11, 4:32 pm, Erland Sommarskog wrote:
> James Fraser (j...@concentric.net) writes:
> > I am working with a third party database. They are storing some data
> > that I need to use in a binary field. I've got the code to parse the
> > binary and reconstruct what I need. Unfortunately, there might be
> > multiple "entries" stored in a single binary field. a certain byte,
> > let's just say the first, will always be the count of "entries" in
> > this particular SQL entry.
> > An example:
> > then entry might be:
> > 0x01000012341234
> > where 12341234 is the data entry that I will parse.
> > Another possible entry is:
> > 0x03000012341234567856789ABC9ABC
> > The first byte indicates that there are three data values I want to
> > parse out:
> > 12341234
> > 56785678
> > 9ABC9ABC
> > The portions of the binary I need are always the same length and there
> > may be from 1 to ~100 of them. (usually 1 if it matters.)
>
> > The big question:
> > How could a SQL query return an entry for each of the "entries" in the
> > binary field? For the second example I would want three entries in my
> > results, each row returning a different section of the binary data.
> > For the first, only one row. I'll be querying the set and expecting to
> > get back more results than the number of entries in the set.
>
> Have a look at my web site, at http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-lengt h.
> There is an example with binary values further down.
>
> In order to apply the technique on a table column see the section http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists.
>
> There is an SQL 2000 version of the article as well.

Thanks for the help. This got me going in some decent directions and I
now have a UDF (and a SP) that will take the binary data and return a
table with its contents.

But maybe I didn't think ahead far enough:
How can I run this SP or UDF on all of the rows in my original table.

I want the functionality of something like this:
SELECT * FROM my_fn ( inputtable.row1binaryfield)
UNION
SELECT * FROM my_fn (inputtable.row2binaryfield)
..
..
UNION
SELECT * FROM my_fn( inputtable.rowlastbinaryfield)

where the binary inputs are coming from the original table in the db.
I'm on SQL 2000, BTW. Right now, I have the feeling that a cursor is
the way out of this.


Any thoughts?
Jamie Fraser

Re: How would you... (binary info in a table)

James Fraser (jbf1@concentric.net) writes:
> But maybe I didn't think ahead far enough:
> How can I run this SP or UDF on all of the rows in my original table.
>
> I want the functionality of something like this:
> SELECT * FROM my_fn ( inputtable.row1binaryfield)
> UNION
> SELECT * FROM my_fn (inputtable.row2binaryfield)
> .
> .
> UNION
> SELECT * FROM my_fn( inputtable.rowlastbinaryfield)
>
> where the binary inputs are coming from the original table in the db.
> I'm on SQL 2000, BTW. Right now, I have the feeling that a cursor is
> the way out of this.

Yes, on SQL 2000 you are stuck with a cursor. SQL 2005 offers the APPLY
operator to address this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How would you... (binary info in a table)

On Sep 15, 2:35 am, Erland Sommarskog wrote:
> ...
> Yes, on SQL 2000 you are stuck with a cursor. SQL 2005 offers the APPLY
> operator to address this.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Thanks for the help and pointers!


James Fraser

Re: How would you... (binary info in a table)

James,

I don't think you need a cursor for this. If you create a
permanent table of integers (from 0 to the largest possible
number of items) or a table-valued function returning any
chosen range of integers, you can write this as a single
query. The example below should work, where
master..Nums(@from,@to) is a table-valued function returning
the table of integers between @from and @to.

create table Binaries (
b varbinary(max)
);
insert into Binaries values (0x01000012341234);
insert into Binaries values (0x03000012341234567856789ABC9ABC);
go

declare @itemLen int; set @itemLen = 4;
declare @prefixLen int; set @prefixLen = 3;
select
substring(b,1+@prefixLen+n*@itemLen,@itemLen) as Item
from Binaries
join master..Nums(0,200) as Nums
on Nums.n < cast(substring(b,1,1) as tinyint)
go

-- drop table Binaries

Steve Kass
Drew University
www.stevekass.com


Erland Sommarskog wrote:

> James Fraser (jbf1@concentric.net) writes:
>
>>But maybe I didn't think ahead far enough:
>>How can I run this SP or UDF on all of the rows in my original table.
>>
>>I want the functionality of something like this:
>>SELECT * FROM my_fn ( inputtable.row1binaryfield)
>>UNION
>>SELECT * FROM my_fn (inputtable.row2binaryfield)
>>.
>>.
>>UNION
>>SELECT * FROM my_fn( inputtable.rowlastbinaryfield)
>>
>>where the binary inputs are coming from the original table in the db.
>>I'm on SQL 2000, BTW. Right now, I have the feeling that a cursor is
>>the way out of this.
>
>
> Yes, on SQL 2000 you are stuck with a cursor. SQL 2005 offers the APPLY
> operator to address this.
>
>

Re: How would you... (binary info in a table)

On Sep 15, 11:20 pm, Steve Kass wrote:
> James,
>
> I don't think you need a cursor for this. If you create a
.. . . [ nice example using a number table deleted.]

I like what you're suggesting, but one detail that I thought would be
easy seems not to be. For each binary piece, my parsing function needs
to return three fields, of different datatypes.

When I first asked this question, I thought I could do something like
select * from my_parser_fn(select b from Binaries).

I could divide the parser into three copies, one for each field, but
they are inter-related, and I would like to keep that code in one
place.

This looks like another pointer towards a cursor. I can be proud
though, that in a year of part time SQL work, this will be my first
cursor. And I have a procedural language background, too. What will
happen once I fall off the wagon?

(Another complication that I discovered on some test runs and
inspections. Not all of the binary parts are really the same length. I
can get around this without much difficulty, I just need to parse some
headers a little more closely for each binary thing.)


Thanks again all for the help...
James Fraser