Menu
I have a SQL Server database table with a varbinary(max) column (i.e. Data VarBinary(max) in a create table command).
Is it possible to do a where clause with some kind of pattern matching within the binary data?
For example, using the C# .NET SqlCommand object, I found that I can do a query like select * from TableName where Data = 0x4638763849838709 go, where the value is the full data column value. But I want to be able to pattern match just parts of the data, like select * from TableName where Data = 0x%3876% go.
Thanks.
421
In SQL, binary data types are used to store any kind of binary data like images, word files, text files, etc. in the table. In binary data types, we have an option like allowing users to store fixed-length or variable length of bytes based on requirements.
The syntax for declaring Binary variable is binary(n) , where n defines the size in bytes. Note that size is in bytes and not number of characters. For Example, when we declare as binary(10) , The column will occupy 10 bytes of storage. The value of n can be from 1 to 8000 bytes.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters.
The MySQL BINARY function is used for converting a value to a binary string. The BINARY function can also be implemented using CAST function as CAST(value AS BINARY). The BINARY function accepts one parameter which is the value to be converted and returns a binary string. Syntax: BINARY value.
For the example you have given in the question
WITH t(c) AS
(
SELECT CAST(0x4638763849838709 AS VARBINARY(MAX))
)
SELECT *
FROM t
WHERE CHARINDEX(0x3876,c) > 0
SQL Server doesn't provide any functions to search through VARBINARY fields, so this is not possible.
See this related SO question and answers.
Essentially, you will need to extract the binary information and use a tool that understands the format it is in to search through it. It can't be done directly in SQL Server.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
