sql server (alternate):
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

James Fraser (jbf1@concentric.net) writes:
[quoted text, click to view]

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.


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

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

[quoted text, click to view]

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

James Fraser (jbf1@concentric.net) writes:
[quoted text, click to view]

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/downloads/books.mspx
Books Online for SQL Server 2000 at

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


[quoted text, click to view]