0

I have a table with rows containing binary data as seen below. (I would copy and paste but it is huge)

Collection of rows containing binary data

When I take the data from the first row and substitute it for the variable 'x' in the below query I receive no results despite it clearly existing in that table.

SELECT *
  FROM table
  WHERE Logo = x

I created a mock table to test this type of query which contains these two values;

0x123456 0x7890

When I execute the following query I am greeted with the correct result.

SELECT *
  FROM TABLE
  WHERE Column = 0x123456
Krist0f
  • 63
  • 9
  • What do you mean by "insert it into 'x'" – Magnus Jun 23 '20 at 08:31
  • @Magnus say I have the value 0x12345 in the row, I want to take this value and search the table for it. i.e SELECT * FROM table WHERE Logo = 0x12345 – Krist0f Jun 23 '20 at 08:40
  • So your mock table works but not the real? – Magnus Jun 23 '20 at 08:46
  • @Magnus Yes that is correct. – Krist0f Jun 23 '20 at 08:49
  • What is your engine? SQL Server, MySQL or something else? – igg Jun 23 '20 at 09:02
  • You binary data looks large, are you searching for the complete value? – Magnus Jun 23 '20 at 09:19
  • @igg SQL Server, 2016 to be specific. – Krist0f Jun 23 '20 at 09:31
  • @Magnus Yes I am looking for a complete value. – Krist0f Jun 23 '20 at 09:31
  • What is the exact query that does not work? Also can you confirm the datatype of the table column. – Magnus Jun 23 '20 at 10:29
  • Where are you running the query? If it's via Management Studio then you may be running into the max character limit of 8192. See [this SO question](https://stackoverflow.com/questions/952247/sql-server-truncation-and-8192-limitation) – strickt01 Jun 23 '20 at 10:32
  • @Magnus I'm afraid I cannot write the exact query. But it contains a varbinary value that is 65,535 characters long which leads me onto strickt01 's question. See next comment. – Krist0f Jun 23 '20 at 10:59
  • @strickt01 I followed the steps in this to export but I am greeted with this error The data type for "Destination - temp_txt.Inputs[Flat File Destination Input].Columns[Logo]" is DT_IMAGE, which is not supported. Use DT_TEXT or DT_NTEXT instead and convert the data from, or to, DT_IMAGE using the data conversion component. This is a technically a separate question now. – Krist0f Jun 23 '20 at 10:59
  • You need to set the data type for the column to something other than DT_IMAGE to export it to a flat file. You can do this in the expot wizard. Failing that, try the answer on the SO question from Luke Sampson re. saving the results to a CSV file. – strickt01 Jun 23 '20 at 11:24
  • Why do you need to query on a binary value in the first place. It seems very odd. Is this part of an application or a one time query? – Magnus Jun 23 '20 at 12:00
  • @Magnus we need to update specific rows that contain the same data. The database setup produces a lot of redundant data unfortunately :( – Krist0f Jun 23 '20 at 13:15
  • You could create a hash of the binary data and store that and than a hash of the data you want to find. And than compare the hashes instead of the binary data. – Magnus Jun 23 '20 at 13:41

0 Answers0