Solved

DISTINCT KEYWORD for image data type column

Posted on 2011-03-08
6
441 Views
Last Modified: 2012-06-27
how want to use distinct key word for image datatype in sql server
0
Comment
Question by:Kanigi
  • 3
6 Comments
 
LVL 6

Expert Comment

by:jonaska
Comment Utility
I would suggest calculating a checksum for the data, and select distinct on that. Maybe you can use a managed function to calculate the checksum?
0
 

Author Comment

by:Kanigi
Comment Utility
What is meant by calculating a checksum?
0
 
LVL 6

Expert Comment

by:jonaska
Comment Utility
I thought of SHA1 or MD5 over the Image data.

In the below example MyManagedFucntion could simply return the checksum for the data in MyImageColumn.

SELECT MAX(idCol), dbo.MyManagedFucntion(MyImageColumn)  FROM myTable
GROUP BY dbo.MyManagedFucntion(MyImageColumn)

Open in new window

0
 
LVL 6

Expert Comment

by:jonaska
Comment Utility
Maybe this will explain better: http://en.wikipedia.org/wiki/Checksum
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
You have a couple of options:
1.  As alluded to previously, use the HASHBYTES() function to get a hash of the image.
2. Use the undocumented function fn_varbintohexstr as in:
SELECT DISTINCT master.dbo.fn_varbintohexstr(YourColumnName)
FROM YourTableName

Both options have the caveat that they will only consider the first 8000 bytes.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now