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.
| Author |
Topic |
|
rn5a
Starting Member
25 Posts |
 Posted - 2008-12-24 : 02:43:00
|
A table in SQL Server 2005 has a column named Description (varchar(500)). The problem is though some records under this column are identical, SELECT DISTINCT(Description) retrieves the same record more than once. Please have a look at the image below:
Note the highlighted records. SELECT DISTINCT(Description) has retrieved each of these records twice though the duplicates are identical.What's causing this erratic behavior?Please note that I am running the SELECT DISTINCT(Description) query in a ASP.NET page & retrieving the resultset. The image is the preview of the web page.Thanks,Ron |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-12-24 : 04:36:23
|
| It may be that the differences are not visible.....have some of the records got trailing spaces, or embedded non-display characters? could the difference be off to the right of the screen?try select distinct (description) len(description) from mytablethere are also variations of the len function which cater better for variable/fixed length records |
 |
|
|
rn5a
Starting Member
25 Posts |
Posted - 2008-12-24 : 06:24:40
|
quote:
Originally posted by AndrewMurphy
try select distinct (description) len(description) from mytablethere are also variations of the len function which cater better for variable/fixed length records
I executed SELECT DISTINCT(Description, LEN(Description) as you suggested & strangely, the length differs for the identical records as shown in the image below: I find it really surprising.quote:
It may be that the differences are not visible.....have some of the records got trailing spaces, or embedded non-display characters? could the difference be off to the right of the screen?
How is it possible that the differences are not visible? I tried putting a trailing space & found that SQL Server itself trimmed the trailing space. So I don't think trailing space is a problem. What do you mean by non-display characters?Any concrete solution to overcome this problem?Thanks,Ron |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-12-24 : 06:39:07
|
| Check for special characters in the 2 strings , that could be an issue |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-12-24 : 07:10:40
|
| select distinct RTRIM(description) ? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-12-24 : 07:10:56
|
| Spaces & nulls both appear visually as "empty gaps". There are loads of other ASCII byte codes that are not visible either....like Carriage-Returns and Line Feeds.You could look for select distinct trim(description) (or rtim)... this may resolve your trailing spaces problem. |
|
|
|
|
|
|
DISTINCT Retrieving Erratic Result