0

I've got a problem with SQL and image datatype query.

My program added automatically over 2000 images to the database. Unfortunately some of them are broken ( gray pattern instead of image ). I searched with SQL Managment Studio that the broken images have at the end of their binary notation many "02020202020202" data. The type of image content in this database is "image" ( can't change it ).

How can i search thought the entire database for images with 02020202 binary notation?

I try'ed "

Select * from im__Image where im_Preview LIKE '%02020%';

but of course it didn't work.

How can I in fast way get the list of images which are broken?

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
Kim Bob
  • 3
  • 1
  • 2
  • Try it with http://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql : `Select * from im__Image where CAST(im_Preview AS CHAR(10000) CHARACTER SET utf8) LIKE '%02020%';` – AmazingDreams Jun 25 '15 at 13:25
  • 1
    Why can't you change it from image to varbinary(max)? I have done that in place many times and it has never been an issue. The image datatype is deprecated as of 2005. – Sean Lange Jun 25 '15 at 13:25

1 Answers1

0

I think you also need to convert to varchar after varbinary (otherwise you get gibberish)

Given:

CREATE TABLE  im__Image  (  ImageID     INT IDENTITY PRIMARY KEY
                    ,   im_Preview IMAGE  
                    ,   Added       DATETIME DEFAULT GETDATE()
                    )

and some test data

insert into im__Image  (im_Preview) values (0x123123211234320202020202020202020220202021231231)
insert into im__Image  (im_Preview) values (0x12312321123432020202020202020202022020202123123102) -- ends with 02, works with 20K+ DataLength()
insert into im__Image  (im_Preview) values (0x1231232112340980984723421231231)

this query does the trick for embedded 0202 

select *, convert(varchar(max),CAST(im_Preview as varbinary(max)),2) 
from dbo.im__Image  
where convert(varchar(max),CAST(im_Preview as varbinary(max)),2)  like '%0202%';

and this returns just the one image that ends with 02

select *, convert(varchar(max),CAST(im_Preview as varbinary(max)),2) 
from dbo.im__Image  
where convert(varchar(max),CAST(im_Preview as varbinary(max)),2)  like '%02';

as seen below:

Management Studio Output

(no guarantee about performance....)

Stan
  • 985
  • 1
  • 7
  • 12
  • when i'm using your query with modified like -> like '%02' it's outpointing me nothing. i already checked and the pictures which i'm looking for are ending with 020202 ... i need to use like this beacuse some of the image have inside of them the 020202 code and it's correct code.. so i thought that the best way is to look for them by the ending .. where is the problem? my query select *, convert(varchar(max),CAST(im_Image as varbinary(max)),2) from dbo.im__Image where convert(varchar(max),CAST(im_Image as varbinary(max)),2) like '%02' – Kim Bob Jun 25 '15 at 14:35
  • Works for me on SQL 2014, will add specifics to answer – Stan Jun 25 '15 at 14:46
  • i used SELECT @@VERSION and it show me -> Microsoft SQL Server 2014 - 12.0.4100.1 (X64) – Kim Bob Jun 25 '15 at 15:24
  • Your query has the wrong field name, should be im_preview: `select *, datalength(im_preview), convert(varchar(max),CAST(im_Preview as varbinary(max)),2) from dbo.im__Image where convert(varchar(max),CAST(im_Preview as varbinary(max)),2) like '%02' ` – Stan Jun 25 '15 at 15:43
  • I've changed my answer to use your exact column name – Stan Jun 25 '15 at 15:45
  • maybe there is a problem just like You said with 8000 bytes issue? i used query which You suggested , it was runned over 2 minutes and give 0 rows. – Kim Bob Jun 25 '15 at 15:53
  • I tested an image value with `DataLength()` over 20K ending in %02, and it worked. But I have only 4 rows... The performance of this query will be VERY BAD because no index can be used and the deprecated IMAGE datatype requires (pretty much) an extra SQL Page IO per row! But I get the sense this is a one-off to find corrupt images – Stan Jun 25 '15 at 16:16