how i can select records with varbinary type?

I have table in my database that has two column namely data and length. I declare data as a VARBINARY(MAX) and suppose the table has a record with length equal to 5 and data = 0x23 0x00 0x12 0x45 0x34.

Table definition:

CREATE TABLE [dbo].[tbInputFile] (
  [InputFileID] [varchar](512) NOT NULL,
  [Filename] [varchar](512) NULL, 
  [Filesize] [bigint] NULL, 
  [Content] [varbinary](max) NULL
);

The question:

Is it possible to search (with a SELECT query) to find records that has 0x00 0x12 in their data column?

Answer

Try this SQL query. Inner table contains test data with binary data type. 0x48 is the data that I’m searching in the test_col1 column.

SELECT T.* 
FROM
    (SELECT CAST('Hello' AS VARBINARY(MAX)) AS test_col1,
            CAST('World' AS VARBINARY(MAX)) AS test_col2) AS T
     WHERE CAST(test_col1 AS VARCHAR(MAX)) LIKE '%' + CAST(0x48 AS VARCHAR(MAX)) + '%'