[SOLVED] How can I search for a sequence of bytes in SQL Server varbinary(max) field?

How can I search for a sequence of bytes in SQL Server varbinary(max) field?

I am trying to write a query on SQL Server 2012 that will return varbinary(max) columns that contain a specified byte sequence. I am able to do that with a query that converts the varbinary field to varchar and uses LIKE:

SELECT * FROM foo
WHERE CONVERT(varchar(max), myvarbincolumn) LIKE 
  '%' + CONVERT(varchar(max), 0x626C6168) + '%'

where "0x626C6168" is my target byte sequence. Unfortunately, this works only if the field does not contain any bytes with the value zero (0x00) and those are very common in my data. Is there a different approach I can take that will work with values that contain zero-valued bytes?

Solution

If you use a binary collation it should work.

WITH foo(myvarbincolumn) AS
(
SELECT 0x00626C616800
)
SELECT *
FROM   foo
WHERE  CONVERT(VARCHAR(max), myvarbincolumn) COLLATE Latin1_General_100_BIN2 
                    LIKE '%' + CONVERT(VARCHAR(max), 0x626C6168) + '%'

You might need (say) Latin1_General_BIN if on an older version of SQL Server.