Re: Case insensitive queries

From: Peter J. Holzer <hjp-usenet2_at_hjp.at>
Date: Sun, 30 Nov 2003 15:57:22 +0100
Message-ID: <slrnbsk1ai.qpi.hjp-usenet2@teal.hjp.at>

On 2003-11-29 23:17, Galen Boyer <galenboyer_at_hotpop.com> wrote:

> On Sat, 29 Nov 2003, damorgan_at_x.washington.edu wrote:

>> Galen Boyer wrote:

>>>>And which part of UPPER(x) = UPPER(y) is to radical to
>>>>consider?
>>> None.  But having to do this is a workaround.  Simple.  What
>>> about that is hard to consider?

>>
>> But a switch in the init.ora that does the same thing isn't?
>> The code above will run on any version of Oracle going back to
>> the stone age. The code you write dependent on the switch will
>> work only if on 10g.

> 
> But upper(x) = upper(y) only works when you want
> case-insensitive.  What if you now need case-sensitive?  That
> query won't work.  So, it is two queries.

Yes, of course. They are two different questions, so I write them as two different queries.

What are you going to do with an init.ora parameter? Bounce the database each time someone wants to switch from case-sensitive to case-insensitive queries and back? What if you need both at the same time (e.g., case-sensitive comparison on message-ids and case-insensitive comparison on domain names in the same query?).

Now a function for doing case-insensitive queries (or - more generally - locale-specific comparisons) or maybe even a session parameter is a good thing. It makes explicit what you are doing and saves you some coding and design decisions. I don't see how they could work any better than function-based indexes do now.

        hp

-- 
   _  | Peter J. Holzer    | In this vale
|_|_) | Sysadmin WSR       | Of toil and sin
| |   | hjp_at_hjp.at         | Your head grows bald
__/   | http://www.hjp.at/ | But not your chin.           -- Burma Shave

Received on Sun Nov 30 2003 - 08:57:22 CST