CODEWITHSUNDEEP
sqlsql-serverduplicatesdistinct
Sampath
Sampath

Reputation: 65890

SQL DISTINCT is not removing duplicates

I have used below query to remove duplicates.But still it shows duplicates.Can you tell me why? 

select DISTINCT a.Company 
from [Legacy].[dbo].[Attorney] as a

enter image description here

Update : Below one is also not working.Can you tell me why ? 

select DISTINCT ltrim(rtrim(a.Company)) from [Legacy].[dbo].[Attorney] as a

Update 2 : Now,it's showing 3.I don't know why ? 

select  DISTINCT ltrim(rtrim(a.Company)),convert(varbinary(100), a.Company) from [Legacy].[dbo].[Attorney] as a

enter image description here

Upvotes: 4

Views: 8652

Answers (1)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391436

As is evident of your latest screenshot the difference is this:

  • The row with "number" 17 ends with the letter 'n': 6E00 = 0x006E
  • The row with "number" 18 after that n ends with two spaces: 20002000 = 2x 0x0020
  • The row with "number" 19 after that n ends with a CR+LF linebreak sequence: 0D000A00 = 0x000D + 0x000A

So the three names really are different, it's just not a visual difference in the query tool that displays your rows.

To fix this during import you would have to trim whitespace from the end of the string, this should take care of those 3 different characters. Note that LTRIM and RTRIM only removes "blanks" which further down in the documentation suggests that only spaces are considered. You would thus have to use a different trim function, like in the programming language you're using, to do this trimming.

Upvotes: 6

Related Questions