A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
JamesDid you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>|||I "read", I did not "try", and it appeared that it would only work with
character fields. I feel stupid. Thanks, I tried it, that works.
My next task is to write the entire IMAGE field to a disk file so that it
may be opened and examined by an external program.
James|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
James
Did you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>
|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
JamesDid you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A quick counting question

Once more I searched around but couldn't get a good handle on it.

The problem is this:

1) I send a notifier out to X no. of people
2) Y no. of people click on the notifier
3) I need to be able to get the percentage of Y/X

table structure tblNotifier:

OBJID
Recipient
Return_Status

I can do it with 2 queries, but I'd love to do it with 1

Select count(*) from tblNotifier;
Select count(*) from tblNotifier where return_status=1;

and then do the math in ASP, but... really, that's just not a whole lot of fun.

Thanks,
Robhow about : --

select sum (convert(numeric,Return_Status))/convert(numeric,count(recipient)) *100 from tblNotifier|||Originally posted by Enigma
how about : --

select sum (convert(numeric,Return_Status))/convert(numeric,count(recipient)) *100 from tblNotifier

'k How much does an explanation of why that works cost?

Thanks, Rob|||select
sum (convert(numeric,Return_Status)) -- Take the return status (1 for those who have replied , 0 for not replied) and sum it up
/ -- division
convert(numeric,count(recipient)) -- no of rows

*100 -- for percentage purposes

from tblNotifier -- Your table

;)|||I'm just an ass evidently.. 20 seconds of thought and I see why that works. All apologies

And thank you very much for pointing out my intellectual shortcomings :)

Rob|||Again ..
In case Return_Status is int ... you can simply say ...

select sum (Return_Status)/convert(numeric,count(recipient)) *100 from tblNotifier

though i assume it should be of type bit|||I'm just straight summing it becuase it is of type int. I was worried that they would add some random criteria for return_status, and have 3 or 4 different status options. So this way, it's not a big pain to add another table and reference it in.

But it works great, so thanks a bunch.

Rob