Finding duplicate values in

[RhythmAddict112]

Hi all. First fof, I'm using Oracle 9i. I have a column and a pretty small table, 10k rows. There is a record for each user that has used this table. One of the columns is their domain ID. I have recently discovered that some individuals have domain ID's with mixed lower, and upper case letters...since Oracle is case sensitive, that is not a good thing. I can easily convert everyone to lower case, however how can i find the duplicate entries? Keep in mind...A duplicate ID as far as I'm concerned is teh same domain ID, whether it is upper or lower case....Any ideas? I appreciate any suggestions you can give me on this.

Example:

Name: Bob Green
Domain ID: GreenBo


ID DomainID
________________
100 GreenBo
110 greenbo

I would need to find both of these, and keep one based on the other fields...

 

[PHV]

Don't know Oracle but I presume you can use a function, say LOWER, to convert a string to lowercase.
A starting point to find the duplicates:
SELECT LOWER(DomainID), Count(*) FROM yourTable GROUP BY LOWER(DomainID) HAVING Count(*)>1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244