4

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.

Community
  • 1
  • 1
Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431

3 Answers3

8

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

SELECT [cpclid] 
FROM [cp]
where cast(cpphoto as varbinary(4)) <> 0xFFD8FFE0
Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431
  • +1. Worked a treat, so easy in the end.. I used it to find any files not zlib'd : `WHERE CAST( [document] as varbinary(2)) <> 0x798C` – Scotty.NET Mar 13 '13 at 09:19
6

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • My job is to take software from another software's database and migrate it in to our database (which by the way does use varbinary(max)). Once I cast it to a varbinary I still have the same problem. Please post a solution on how to solve this with a varbinary and I will remove my -1 I did. – Scott Chamberlain Aug 01 '11 at 18:20
  • I apologize for not providing enough information to meet your requirements to not get down-voted. I thought it was implied that if you want to do a string comparison you'd also have to convert to varchar (as this information was already given in other answers). So did you try `SELECT TOP 1 CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), image_column)) FROM table_name;`? What did it look like? – Aaron Bertrand Aug 01 '11 at 18:31
  • This works for GIFs (I see Gif89a at the beginning of the output) but I don't have any true JPGs stored as blobs to try. – Aaron Bertrand Aug 01 '11 at 18:38
  • That query returns a empty string. However using a cast instead of a convert on the inner conversion gives me 'ÿØÿà' which I can compare. See my updated original post on the solution I found, if you have a better solution I would greatly appreciate it. (SO will not let me remove the down vote until your edit your answer.) – Scott Chamberlain Aug 01 '11 at 18:42
  • CAST gives you a different answer than CONVERT? It sounds like you've found an unknown bug in SQL Server. Can you demonstrate this using a StackExchange query (and how you are comparing yoya to your original search criteria)? – Aaron Bertrand Aug 01 '11 at 18:45
  • Weird, now it's not doing the difference, I swear it was doing it before. I chalk the original blank result to user error on my part. – Scott Chamberlain Aug 01 '11 at 18:53
2

Use where cpphoto not like 'FFD8FFE0%' in your where clause.

cast cpphoto as a varchar(max) if it is not a string already.

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Ross Barbish
  • 680
  • 6
  • 22
  • Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type image to varchar(max) is not allowed. – Scott Chamberlain Aug 01 '11 at 16:58
  • http://cavemansblog.wordpress.com/2009/04/24/sql-how-to-convert-image-data-type-to-plain-text-and-back/ Oh, use Convert instead, 2nd select statement at link. – Ross Barbish Aug 01 '11 at 17:03
  • It does not work as you think it works, that is actual text stored in a binary field, I need to match a binary pattern, not a text pattern [See the 3rd column](http://data.stackexchange.com/stackoverflow/q/108055/searching-a-image-for-a-pattern) – Scott Chamberlain Aug 01 '11 at 18:27
  • It worked but I had to tweak it a bit, I will update the main post on how to do it. – Scott Chamberlain Aug 01 '11 at 18:35