I am trying to return a distinct set of department names from a table - nothing special. However, when using the following query duplicates are displayed:
select distinct department_name from dbo.departments;
I also tried:
select distinct department_name from dbo.departments group by department_name;
So this led me to believe that I might have hidden characters in the values and sure enough, when I checked the length of the strings they returned different values. So, I decided to locate the hidden characters using the function from this question on stack overflow. Strangely enough this only return SPACE. I then tried the following query and it made no difference at all:
select distinct ltrim(rtrim(department_name)) as department_name from dbo.departments;
Intrigued, I cast the values to
VARBINARY and noticed that they have the exact same binary value, and performing a
DISTINCT on the binary value does indeed produce a unique result set.
I have tried casting between
NVARCHAR and to a different collation also (the values are in the same column, within the same database using Latin1_General_CI_AI). I really need to be able to get a distinct set from this table. Does anybody know what might be causing this issue?
After some further investigation, this issue seems to only happen for strings that end with the hex value
0xA000. Any value in the column not ending with this character are fine.
If I remove the
0xA000 character from the string I can then apply
DISTINCT as normal like so:
DECLARE @binary VARBINARY(8) = 0xA000; DECLARE @string VARCHAR(8) = CONVERT(VARCHAR(MAX), @binary); UPDATE dbo.departments SET department_name = REPLACE(department_name, @string, '');
But this won't work long term as users can update this table and I would need to adjust every query to do a replacement in the
WHERE clause. I am using a workaround right now which is no more than using
MIN to return the entry with the shortest length. This is less than ideal as the problem with distinct is also affecting most of the other language elements as well, for example
ORDER BY, any window functions and