From: Peter Brawley
Date: May 9 2006 2:34pm
Subject: Re: Case confusion
List-Archive: http://lists.mysql.com/mysql/197793
Message-Id: <4460A88F.5080307@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Marcus,
> 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?
If you need to inspect the dupes, rather than eliminate them on some 
criterion without inspection, this will fetch them:

SELECT LOWER(emailaddress), ...
FROM mytable
GROUP BY LOWER(emailaddress)
having COUNT(*) > 1;

PB

>
> thanks,
>
> Marcus
> --Marcus Bointon
> Synchromedia Limited: Putting you in the picture
> marcus@stripped | http://www.synchromedia.co.uk
>
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=peter.brawley@stripped
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 5/5/2006
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006