CODEWITHSUNDEEP
sqlsql-serverwhere-clause
John Thompson
John Thompson

Reputation: 161

SQL WHERE clause with binary

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.

Upvotes: 15

Views: 24946

Answers (4)

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

SELECT * 
FROM myTable 
WHERE CONVERT(varchar(max),myvarbinaryfield,2) like '%46387638%'

perhaps I should have used the OP's example:

select * 
from TableName 
where CONVERT(varchar(max),Data,2) like '%3876%'

Explanation: The characters 0x aren't added to the left of the converted result for style 2. Please see the Microsoft documentation for converting varbinary data.

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#binary-styles

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453298

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

Upvotes: 15

Oded
Oded

Reputation: 499012

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.

Upvotes: 4

tobias86
tobias86

Reputation: 5029

Taking a shot in the dark here, but you could convert the field to VARCHAR(MAX) first, then compared using a LIKE statement.

E.g. 

SELECT *
FROM TableName
WHERE CONVERT(VARCHAR(MAX), Data) LIKE '%2345%'

Upvotes: -1

Related Questions