List:General Discussion« Previous MessageNext Message »
From:Marcus Bointon Date:May 9 2006 12:37am
Subject:Case confusion
View as plain text  
I just noticed that a key field (emailaddress) in my db is case  
sensitive when it should not have been, so now I've got a bunch of  
what are effectively duplicate records. I'm having trouble picking  
them out so I can manually merge/delete them before changing the  
collation on the field to be case insensitive.

SELECT * FROM mytable group by lower(emailaddress) having count 
(emailaddress) > 1

This is ok, but it only shows me the records with lower case  
addresses (I can't tell which case version is the correct one without  
looking at them) when I want to see the records with all cases, that  
is all of 'joe@stripped', 'Joe@stripped' and 'JOE@stripped'.  
I'm confusing myself with the case sensitivity and self-references!

I think there are about 45 duplicates out of about 200,000.

How can I find these pesky things?

thanks,

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Putting you in the picture
marcus@stripped | http://www.synchromedia.co.uk

Thread
Case confusionMarcus Bointon9 May
  • Re: Case confusionChris9 May
    • Re: Case confusionMarcus Bointon9 May
      • Re: Case confusionMarcus Bointon9 May
  • Re: Case confusionPeter Brawley9 May