Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    46

    Unanswered: How to making oracle case-insensitive

    Hi,

    How can we make oracle case-insensitive like SQL Server.

    1. For example, 'TOM', 'tom', 'Tom' should be same.
    2. If we have unique index on emp_name, and one of above is there in the table, it should not allow insertion of other.
    3. Select * from emp where emp_name = 'TOM', should return any tom.

    Thanks

    Prashant

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) This design is flawed to allow mix case into the DB.
    2) you'll need to use UPPER or LOWER; which will preclude use of index & has scalability problems.
    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.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Why not just use a mixture of LOWER and UPPER to compare values and index on emp(upper(emp_name)) and emp(lower(emp_name)) accordingly.

  4. #4
    Join Date
    Feb 2005
    Posts
    57

    Lightbulb

    You could add an extra column to the table specifically as an index column which will hold either upper/lower text (your preference) and convert the text on insert into this column.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by anacedent
    1) This design is flawed to allow mix case into the DB.
    2) you'll need to use UPPER or LOWER; which will preclude use of index & has scalability problems.
    1) Often true, but not always. Sometimes we need to preserve the text in mixed case so that it appears correctly when selected. Names like 'MacDonald' and 'Van der Valk' come to mind, for which Initcap doesn't cut it.
    2) Precludes the use of regular indexes yes; but you can index UPPER(emp_name) and then change the search to "Select * from emp where UPPER(emp_name) = 'TOM'" and that will use an index.

  6. #6
    Join Date
    Mar 2004
    Posts
    46
    I did following and it worked:

    1. Created function based index unique lower(emp_name)
    2. In all WHERE clause now using lower(emp.emp_name) = lower(emp_name_in).
    3. Created trace, it is using the index in all the cases.
    4. It is storing the string as it is, and not allowing duplicates ('tom' = 'TOM' = 'Tom').

    Thanks

    Prashant

Posting Permissions

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