Finding Duplicates to Manually Alter and Delete

[nkomokov]

I have a very messy database that I'm trying to sort through.

I need to find the records that have duplicates, manually ensure that one record has ALL of the information, then manually delete the extras. So say for example, I want to find ALL the records with duplicate email addresses and view all fields. (AND what happens if there is a space before one of the emails will it still show up as a duplicate?)

 

[nkomokov]

Oh and I would like it to sort the email addresses alphabetically.

Thanks!

 

[druer]

1. In the case of extra spaces in the email simply do an LTRIM and/or RTRIM appropriately. (I've put them both in to remove leading and trailing spaces)

2. The following command should return what you are looking for (duplicates) of any/all email addresses. You could then look them up to figure out what rows you want to keep/deleted.

select count(*), LTRIM(RTRIM(EmailAddress))
from [workorder-tbl]
group by LTRIM(RTRIM(EmailAddress))
having count(*) > 1
order by count(*) desc