Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003

    Unanswered: Finding duplicate values in the same table...

    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.


    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...

  2. #2
    Join Date
    Oct 2004
    Use the upper or lower function and group by to first find the duplicates.

    select lower(domainid), count(*) from table
    group by lower(domainid) having count(*) > 1

    Eliminate the record and update the records to case you wish to use.

  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    select upper(domainid), count(upper(domainID))
    from small_table
    group by UPPER(DOMAINID)
    having count(upper(domainID)) > 1;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2003
    Thanks guys, I'll give it a go when I get back to work!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts