Re: Case insensitive comparisons - no way?

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: 2000/05/22
Message-ID: <3929FFDC.3C9F6E93@ntsource.com>#1/1

Initcap has its problems as well. For example,

SQL> select initcap('scott''s') from dual;

INITCAP

---

Scott'S

One does not expect the 'S at the end. And there is also the "MacDonald" that gets converted to "Macdonald" using initcap.

I suspect one would have to rework initcap to get it to do what one wanted in any case.

However, storing data in mixed case is like storing a finer precision decimal number for a measurement. It might be worthwhile asking if that finer precision is accurate. That is, are the users careful enough to enter correct data at the greater precision? When they mean to enter "MacDonald", do they think "Mcdonald" is good enough? It is easy to check this if there is data already in the system.

If mixed precision is desired and the users have committed to supporting the quality it implies, one alternative to duplicating the entire column is to duplicate only the first 10 or so characters of the column in upper case. This may get the desired result using an index with minimal overhead and without purchasing the Enterprise Edition.

Frank Hubeny

Mark D Powell wrote:

> You say you need to display the data in Mixed case. Would it be
> possible to store the data in upper or lower case and display it
> using inticap which displays the first character in upper case
> and the rest of the column in lower case?
>
Received on Mon May 22 2000 - 00:00:00 CDT