Re: case insensitive query

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Wed, 04 Nov 1998 13:52:00 GMT
Message-ID: <36405b67.8165210@newshost.us.oracle.com>

On Tue, 03 Nov 1998 16:26:22 -0800, Jason Jay Weiland <archduke_at_uclink4.berkeley.edu> wrote:

>Min,
>
> You could try using the UPPER or LOWER charcter
>functions:
>
>SELECT first_name, last_name
> FROM people
> WHERE LOWER(last_name) = LOWER('Wang')
> AND UPPER(first_name) = UPPER('Min');
>

Keep in mind that with the above query, when you apply a function to the database column (and not just the criteria), you will not be using an index. Oracle has to read every column value, apply the function, and then perform the comparison...resulting in a full-table scan of the people table.

A great way to do this would be to store your data value for first_name and last_name in some common case (either upper or lower) and then apply the appropriate function to only the criteria in your query.

>Jay!!!
>

Thanks!

Joel

Joel R. Kallman Oracle Government, Education, & Health

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com

---

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Wed Nov 04 1998 - 07:52:00 CST