. Comparison of image type data in SQL Server

Original:. Comparison of image type data in SQL Server

In SQL Server, if you are comparing data for text, ntext, or image data types. You will be prompted:the text, ntext, and image data types cannot be compared or sorted unless the is NULL or Like operator is used. However, image does not support like comparisons.
So how do you compare the pictures in the database?
For the processing of this large object, there are special function Dbms_lob.compare in Oracle, and there is no special processing function in SQL Server.
You can only intercept data from image data by using the SUBSTRING function for a period of time, up to a maximum of 8060 bytes (8k),
The varbinary type data is then compared. Here is an example of a function that compares image: Note: the type of the local variable is any system-provided or user-defined data type. The variable cannot be a text, ntext, or image data type, please use it directly in the query.

/** * * * object:userdefinedfunction [dbo].    [Funccompareimage] Script date:2016-09-01 11:18:53 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOALTER FUNCTION [dbo].[Funccompareimage](@a1 IMAGE,@a2 IMAGE )RETURNS INT--if match, return 1 as    BEGIN        DECLARE @n INT ,            @i INT ,            @j INT; DECLARE @b1 VARBINARY(8000) ,            @b2 VARBINARY(8000); SET @n = 1; IF datalength(@a1)<> datalength(@a2)--different length            SET @n = 0; ELSE            BEGIN                SET @i = 0; SET @j =(datalength(@a1)- 1)/ 8000 + 1;  while @i <= @j                    BEGIN                        SET @b1 = SUBSTRING(@a1,@i * 8000 + 1,                                             Case @i                                               when @j                                               Then datalength(@a1)% 8000                                              ELSE 8000                                            END); SET @b2 = SUBSTRING(@a2,@i * 8000 + 1,                                             Case @i                                               when @j                                               Then datalength(@a2)% 8000                                              ELSE 8000                                            END); IF @b1 <> @b2                            BEGIN                                SET @n = 0;  Break; END; SET @i = @i + 1; END; END; RETURN(@n); END;

. Comparison of image type data in SQL Server