Posted by James Fraser on 09/14/07 22:39

On Sep 11, 4:32 pm, Erland Sommarskog <esq...@sommarskog.se> 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-length.
> 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

[Back to original message]