DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2001



    On my Oracle instances, string "aaa" does not equal "AAA". In other words, if I have a table MyTable with a unique index on MyTable's single char(3) field. The commands:

    insert into MyTable values ('aaa');
    insert into MyTable values ('AAA');

    work fine. How do I make the instance case sensitive, so that the second insert above would error with a unique key violation?

    Thanks in advance!

    Joe B.

  2. #2
    Join Date
    Aug 2000
    index on upper(keyvalue);

  3. #3
    Join Date
    Nov 2000
    Baltimore, MD USA
    For a little more info:

    Basically, Oracle has no ability to create a case-insensitive instance, as amazing as that seems! So, before 8i, if you wanted case-insensitive searches, you were guaranteed table scans. With 8i, this has been alleviated, but IMHO, it is still *no excuse* for not providing a case-insensitive option in the database!!

    Rant over

    KMesser is *almost* right. If you want to guarantee that 'aaa' and 'AAA' cannot be inserted into the same table, create a *unique* function-based index on UPPER(colname);

    This same index will also allow your case-insensitive searches to be indexed look-ups, providing that the WHERE clause matches the function-based index. ie WHERE UPPER(colname) [=|LIKE|...]...


    - Chris

Posting Permissions

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

Click Here to Expand Forum to Full Width

By using this site, you agree to the Privacy Policy