How to compare varchar with varbinary in SQL Server?

I have a table that holds image formats, eg:

'jpg', 'FFD8FFD8'
'png', '89504E470D0A1A0A'

I have another that holds images where the column type is image.

How can I write a query that compares the image in the images table to the image formats table and return the image type (eg, jpg or png)?

Ideally the prefix column should have been varbinary(max). Also the image data type is deprecated and should be varbinary(max) as well.

Be that as it may, you can use SUBSTRING to check whether the prefix matches. Use a CROSS APPLY lateral join to pre-convert the value before you compare it.

SELECT *
FROM files f
JOIN formats fm
    CROSS APPLY (SELECT CONVERT(varbinary(max), fm.prefix, 2)) v(prefix)
  ON SUBSTRING(f.data, 1, LEN(v.prefix)) = v.prefix;

db<>fiddle

On a side note, the header for JPEG is only fixed at FFD8 and BMP is 424D.