Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Question Unanswered: Case-Sensitive Search for/and update Duplicate Entries in Oracle 11g

    Hello Everyone,
    This is my first time posting a question to any online support community. So please excuse my spotty etiquette on this question.

    I'm using Oracle 11g and am facing a dilemma.

    I have a table called ITEMS as below:

    ID NAME IS_TYPE
    ---- ----- --------
    1 chair 1
    2 Chair 1
    3 cHair 1
    4 chAir 1
    5 CHAIR 1
    6 Table 1
    7 TAble 1
    8 TaBle 1
    9 table 1
    10 Auto_Info 1
    11 Auto_Info 1
    12 Auto_Info 1
    13 Auto_Info 1
    14 Auto_Info 1

    As you can see, the items "chair" and "table" have duplicates with different casing. And what I need to do is to find a way to make the casing consistent and also add a number to the end of each duplicated item. The trick is that this needs to get done ONLY to the duplicates that differ in casing. So the entry Auto_Info should not be touched in any way. The result should look something like this:

    ID NAME IS_TYPE
    ---- ----- --------
    1 Chair 1
    2 Chair 1 1
    3 Chair 2 1
    4 Chair 3 1
    5 Chair 4 1
    6 Table 1
    7 Table 1 1
    8 Table 2 1
    9 Table 3 1
    10 Auto_Info 1
    11 Auto_Info 1
    12 Auto_Info 1
    13 Auto_Info 1
    14 Auto_Info 1

    What I have so far:
    I've spent a lot of time trying to research the internet and merging what I find into a solution. So far I've only been able to find a way to extract the duplicates with different casing from the rest of the table in the following way:


    Code:
    alter session set NLS_COMP=ANSI;
    alter session set NLS_SORT=BINARY_CI;
    SELECT NAME
    from ITEMS
    GROUP BY NAME
    MINUS
    SELECT NAME
    from ITEMS
    GROUP BY NAME
    HAVING COUNT(1)>1
    I'm very new to databases and any help with this problem would be very much appreciated.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The thread in DB2 forum might help you.
    http://www.dbforums.com/db2/1702528-...e-entries.html


    In my opinion,
    the one of remarkable functionality of DB2 related to this isuue might be the lack of support of Case Insensitive search.
    (AFAIK, DB2 doesn't support Case Insensitive search directly.)
    So, there might be another more elegant solution utilizing Case Insensitive search, on other DBMS.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 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.

Tags for this Thread

Posting Permissions

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