Compare Strings in Oracle

Question

I need to query a table for values given a string. The table is case sensitive but I want to do a ToLower() in the comparison.

Suppose I have a classes table with the following data.

class    teacher
-----------------
Mat101   Smith
MAT101   Jones
mat101   Abram
ENG102   Smith

My query should be something like

Select teacher From classes where lower(class) = 'math101'

Is this the best way to do the comparison?

Update

I have no control over the database or the data. I am a read only consumer.

Answer 1

The downside of the kind of query you are talking about is that it cannot use an index on class (as an index lookup, that is -- it could still be used in a fast full scan of the index).

In modern versions of Oracle, though, you can create an index on LOWER(class) which can then be used by this query.

Answer 2

Here is more information about Function-based Indexes (what Dave was referring to above):

• Using Function-based Indexes for Performance
• Function-Based Indexes

Answer 3

No; it would be better to improve the data: create a numeric ID that represents these seemingly meaningless variations of class (and probably an associated lookup table to get the ID). Use the ID column in the where clause and you should be hitting an indexed numeric column.

If that's no an option, consider a function-based index on lower(class).

If that's not an option, and the question of "best" is strictly relative to performance, consider denormalizing and adding a column that contains lower(class), probably populated with a trigger.

If that's not an option, update the data so that it's all lowercase (and take measures to insert/update only lowercase class data).

If you can't update the data like that, then the answer is "maybe".

In any case, you can't call it best if you haven't tested indexing of the column.

Answer 4

This method requires that you run 10gr2 or better.

Before altering session:

SQL> WITH LETTERS AS
  2  (SELECT 'a' LETTER FROM DUAL UNION ALL
  3   SELECT 'b' LETTER FROM DUAL UNION ALL
  4   SELECT 'A' LETTER FROM DUAL UNION ALL
  5   SELECT 'B' LETTER FROM DUAL) 
  6  SELECT LETTER FROM LETTERS 
  7  WHERE LETTER = 'A';

L
-
A

SQL>

If you are able to alter your session, you can do the following:

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> WITH LETTERS AS
  2  (SELECT 'a' LETTER FROM DUAL UNION ALL
  3   SELECT 'b' LETTER FROM DUAL UNION ALL
  4   SELECT 'A' LETTER FROM DUAL UNION ALL
  5   SELECT 'B' LETTER FROM DUAL) 
  6  SELECT LETTER FROM LETTERS 
  7  WHERE LETTER = 'A';

L
-
a
A

Altering the session as done above causes the database to sort and compare the upper and lower case version of the same letter as equivalent objects. For more information, please see http://www.orafaq.com/node/91

HTH, Gabe

Answer 5

If the variants (Mat101, MAT101, mat101) refer to the same thing, they should have the same identifier.

Depending on the volume of data, frequency of updates, queries etc, you may consider replicating the data into a database that you do control with a cleansing/standardisation stage built into the replication.

While you say "I have no control over the database", if you can query you will have an impact over the database. Someone has control of the database, and it may be worth an email/phone call saying that, if they add a lower() index to that column then it will reduce the impact of your queries on the database.

Finally, if the variants are simple enough then you could try

Select teacher From classes where class in (lower('mat101'), upper('mat101'), initcap('mat1010')).

Answer 6

Since you added that you're a read-only user of the database, the best method is close to what you started with:

Select teacher From classes where lower(class) = LOWER('math101')

Note that I added LOWER() to the input parameter, just to be sure that's lower-case too. Some might call that "belts and suspenders" (aka redudant); I call it good defensive programming.