How to search a image/varbinary field for records that start with a binary pattern

Question

I am trying to find all images that do not start with the magic number ff d8 ff e0 (the signature for jpg) According to the MSDN I should be able to use patindex on my data. However

SELECT TOP 1000 [cpclid]
FROM [cp]
where patindex('FFD8FFE0%', cpphoto) = 0 -- cpphoto is a column type of image

gives me the error

Msg 8116, Level 16, State 1, Line 1 Argument data type image is invalid for argument 2 of patindex function.

What would be the correct way to find records that do not match the magic number of ff d8 ff e0?

UPDATE:

Here is a link to test any suggestions you have.

---

I Ross's solution worked in the end with some tweaking on what the query.

SELECT [cpclid]
  FROM [cp]
where convert(varchar(max), cast(cpphoto as varbinary(max))) not like convert(varchar(max), 0xFFD8FFE0 ) + '%'

---

I found a even better solution, see my answer.

Answer 1

I found a much simpler solution that runs a lot faster.

SELECT [cpclid] 
FROM [cp]
where cast(cpphoto as varbinary(4)) <> 0xFFD8FFE0

Answer 2

Why are you still using the IMAGE data type? It has been deprecated in favor of VARBINARY(MAX)... if you convert your column to VARBINARY(MAX) I think you'll find it a lot easier to work with.

EDIT

In SQL Server 2008 you can use a much easier convert:

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), cpphoto), 2) FROM cpphoto;

In fact this worked just fine on your StackExchange query (I suspect the back end is not using SQL Server 2005).

But I'm glad my answer was so useless to you. Noted to self.