Case sensivivity II
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Case sensivivity II

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    Smile

    Folks!

    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
    Posts
    462
    index on upper(keyvalue);

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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|...]...


    HTH,

    - 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