Re: how to do case insensitive text literal comparison?

In article <70pqmo$5vd$1_at_nnrp1.dejanews.com>,   bonanos_at_yahoo.com wrote:
>
>
> "mdr" <resnimi_at_mail.northgrum.com> wrote:
> > Try the SELECT... WHERE UPPER(COLUMN1) = 'A'
>
> The only problem with that is that if there is an index on COLUMN1 it will
> invalidate it and cause a full table scan.
>
> An option is to automatically change all values inserted into COLUMN1 to
> uppercase.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

Yes, that's always been a problem for case insensitive searches. IF you really need the performance you can add a column to your table with the data in the same case. So if COLUMN1 is the data you wish to search on, add a SEARCH_COLUMN1 which has the same data as the original COLUMN1. Then add an index to speed the searches. Search on the search column and return the mixed case (original) column. So,

SELECT COLUMN1
FROM YOUR_TABLE
WHERE SEARCH_COLUMN1 = UPPER(searchvalue);

This way you can return the original data and still do fast searches.

What it boils down to is the classic space/time trade off, use more table space to gain more speed. This is one of those denormalizing steps.

Here's a final thought, what if ORACLE added a case insensitive search index option? This gets done often enough that it might be worth the effort to put it in the database. opinions anyone???

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own