In SQL Server, if you compare text, ntext, or image data types. The following message is displayed: Data Types of text, ntext, and image cannot be compared or sorted unless the is null or like operator is used. However, image does not support like comparison.
Then how can we compare images in the database.
For processing such large objects, there is a special function dbms_lob.compare in Oracle, and sqlserver does not have a special processing function,
You can only use the substring function to intercept data from the image and put it into varbinary data. The maximum value is 8060 bytes (8 K ),
Then compare the data of the varbinary type. The following is an example of a function to compare images:
If object_id ('compare _ image') is not null drop function compare_imagegocreate function compare_image (@ A1 image, @ A2 image) returns int -- If match, return 1 asbegindeclare @ n int, @ I int, @ J intdeclare @ B1 varbinary (8000), @ B2 varbinary (8000) set @ n = 1if datalength (@ A1) <> datalength (@ A2) -- different lengthset @ n = 0 elsebeginset @ I = 0 set @ J = (datalength (@ A1)-1) /8000 + 1 while @ I <= @ jbeginset @ 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 <> @ b2beginset @ n = 0 breakend set @ I = @ I + 1end endreturn (@ n) endgo
The content is from the log published by my QQ space at, September 17 .. Network forwarding.