title: How to search a image/varbinary field for records that start with a binary pattern
body: <p>I am trying to find all images that do not start with the <a href="http://en.wikipedia.org/wiki/Magic_number_%28programming%29" rel="nofollow noreferrer">magic number</a> <code>ff d8 ff e0</code> (the signature for jpg) According to the <a href="http://msdn.microsoft.com/en-us/library/ms187993%28v=SQL.90%29.aspx" rel="nofollow noreferrer">MSDN</a> I should be able to use patindex on my data. However</p>
  <pre><code>SELECT TOP 1000 [cpclid]
  FROM [cp]
  where patindex('FFD8FFE0%', cpphoto) = 0 -- cpphoto is a column type of image
  </code></pre>
  <p>gives me the error</p>
  <blockquote>
  <p>Msg 8116, Level 16, State 1, Line 1
  Argument data type image is invalid for argument 2 of patindex function.</p>
  </blockquote>
  <p>What would be the correct way to find records that do not match the magic number of <code>ff d8 ff e0</code>?</p>
  <p><strong>UPDATE</strong>:</p>
  <p><a href="https://data.stackexchange.com/stackoverflow/q/108053/searching-a-image-for-a-pattern" rel="nofollow noreferrer">Here is a link</a> to test any suggestions you have.</p>
  <hr />
  <p><s>I Ross's solution worked in the end with some tweaking on what the query.</p>
  <pre><code>SELECT [cpclid]
    FROM [cp]
  where convert(varchar(max), cast(cpphoto as varbinary(max))) not like convert(varchar(max), 0xFFD8FFE0 ) + '%'
  </code></pre>
  </s>
  <hr />
  <p>I found a even better solution, see my answer.</p>