|
|
Posted by Steve Kass on 09/16/07 04:20
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.
>
>
Navigation:
[Reply to this message]
|