how i can select records with varbinary type?

Question

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 1

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)) + '%'