Compare two IMAGE fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare two IMAGE fields

Author

Topic

issam
Starting Member

30 Posts

Posted - 2008-09-10 : 15:08:00

Hi,

how to Compare two IMAGE fields, i try the following:-

SELECT itemphoto.item_no, itemphoto.photo
FROM [2560].[2560].itemphoto itemphoto ,[erpinv41].[ims].itemphoto MAINPHOTO
where itemphoto.photo <> MAINPHOTO.photo

but i get the follwoing error:-

Msg 402, Level 16, State 1, Line 1
The data types image and image are incompatible in the not equal to operator.

best regards

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-10 : 17:19:52

I may be wrong, but i have never heard about comparing image fields.

In the previous versions of MS SQL images are stored as BLOBs, which is a bad design. As its better to store the images in a folder and put the relative path in your db and present your image in the presentation layer. Dont know if this has been changed in 2008, but i doubt it.

Secondly, if you compare two image fields, what do you get in return ? Yes they are the same or a value of true ???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 17:27:59

You can try to use DATALENGTH as first line of check for the two images.
If the DATALENGTH is different, then you have a "different" picture".
You can also use SUBSTRING(photocolumn, 1, 8000) to check first 8000 bytes.
And also SUBSTRING(photocolumn, DATALENGTH(photocolumn) - 7999, 8000) to check last 8000 bytes.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 17:30:00

[code]SELECT itemphoto.item_no,
itemphoto.photo
FROM [2560].[2560].itemphoto AS itemphoto
CROSS JOIN [erpinv41].[ims].itemphoto AS MAINPHOTO
where DATALENGTH(itemphoto.photo) <> DATALENGTH(MAINPHOTO.photo)
or SUBSTRING(itemphoto.photo, 1, 8000) <> SUBSTRING(MAINPHOTO.photo, 1, 8000)[/code]

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-10 : 18:21:03

Definitely a hot guy.

Hey Peter, it should past midnight in sweden. And you are still on fire ???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 18:28:16

Yes, the time is now 12:23am.

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-10 : 19:11:07

its 0:10am here, Great job.

Cut back on the coffee

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-10 : 20:26:04

quote:

Originally posted by Peso

SELECT		itemphoto.item_no,
		itemphoto.photo
FROM		[2560].[2560].itemphoto AS itemphoto
CROSS JOIN	[erpinv41].[ims].itemphoto AS MAINPHOTO 
where		DATALENGTH(itemphoto.photo) <> DATALENGTH(MAINPHOTO.photo)
		or SUBSTRING(itemphoto.photo, 1, 8000) <> SUBSTRING(MAINPHOTO.photo, 1, 8000)

E 12°55'05.63"
N 56°04'39.26"

I believe you can also do these:


		or itemphoto.photo not like MAINPHOTO.photo
		or checksum(itemphoto.photo) <> checksum(MAINPHOTO.photo)
		or binary_checksum(itemphoto.photo) <> binary_checksum(MAINPHOTO.photo)
		or SUBSTRING(itemphoto.photo,  8001, 16000)  <> SUBSTRING(MAINPHOTO.photo, 8001, 16000)
		or SUBSTRING(itemphoto.photo, 16001, 24000) <> SUBSTRING(MAINPHOTO.photo, 16001, 24000)
		or SUBSTRING(itemphoto.photo, 24001, 32000) <> SUBSTRING(MAINPHOTO.photo, 24001, 32000)
		or SUBSTRING(itemphoto.photo, 32001, 40000) <> SUBSTRING(MAINPHOTO.photo, 32001, 40000)
		... and so on ...

If they compare equal in the first cut of tests with checksum, binary_checksum, like, and substring compares of fisrt and last 8000 characters, you could use the datalength of the photos to generate a series of substrings to do a complete test in a dynamic SQL Statement.

If you are using SQL 2005, convert the column over to varbinary(max) to make things simple:

itemphoto.photo <> MAINPHOTO.photo

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-11 : 01:13:21

I don't think checksum work over image columns.
That's the reason I wrote the checksum replacement.

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-11 : 12:28:17

You're right about checksum.

I also tried the LIKE with image and that doesn't work either. LIKE does work with text or ntext, so I guess I just assumed it would work with image.

CODO ERGO SUM

rboehme
Starting Member

1 Post

Posted - 2008-10-30 : 20:15:44

Install the xp_md5 extended system procedure (you can download the md5.dll from the Internet), and run a statement like this:

select * from tablename where dbo.fn_md5x(oldimage, datalength(oldimage)) <> dbo.fn_md5x(newimage, datalength(newimage))

For improving performance, create a separate column named md5x, populate it with the values returned by the function, and change the select statement to look like this:

select imagecolumn from tablename where md5x = @md5x

You can create a local function out of the extended procedure, like follows:

CREATE FUNCTION [dbo].[fn_md5x] (@data IMAGE, @len INT = -1)
RETURNS CHAR(32) AS
BEGIN
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 @data, @len, @hash OUTPUT
RETURN @hash
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources