Re: Case insensitive queries

mcstock wrote:

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1070045315.606163_at_yasure...
> | Galen Boyer wrote:
> |
> | > On Sun, 23 Nov 2003, forbrich_at_yahoo.net wrote:
> | >
> | >
> | >>(Seems to me the requirement for case-insensitivity would be a
> | >>generated by programmer laziness or insufficient user training
> | >>- not uncommon these days.)
> | >
> | >
> | > Why is it that when Oracle has an obvious limitation, people
> | > respond like this? The web is pretty much a case-insensitive
> | > world. Oracle crows about being all "webified" but it doesn't
> | > offer this simple feature. Sure, the data is case-sensitive, but
> | > it would be damn nice for the database to allow the developer the
> | > ability to turn it off or on based on query needs.
> |
> | I disagree completely. When 10g is released I will advise my students to
> | never use the option for case insensitive queries.
> |
> | A properly designed application should force valid case for inserts and
> | updates. Covering up for a bad design or bad implementation is not a
> | good policy. It is only one step removed from making all searches using
> | the SOUNDEX function or making all columns VARCHAR2(4000).
> |
> | So is it a limitation? Absolutely. Is it one I am glad Oracle has
> | remedied? No! Efforts put elsewhere would have been far more valuable.
> | --
> | Daniel Morgan
> | http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> | http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> | damorgan_at_x.washington.edu
> | (replace 'x' with a 'u' to reply)
> |
>
> daniel
>
> there are probably more than one or two steps between case-insensitive
> queries and 'soundex(4000)' ;-)
>
> the business requirements that i've had to fulfill have not been due to lazy
> untrained personnel -- recently the good people at the state agency that i
> developed a system for were able to get more work done with less effort,
> while gradually being able to clean up legacy data, by having for the first
> time case-insensitive queries. (reaction 'wow! you can do that? that'll let
> us do thus and so and etc.etc.)
>
> it would be nice if all data could be squeaky clean at all times, but in
> developing systems used by real people, there is always a cost/benefit
> trade-off between more extensive business rules and a reasonable
> implementation of a usable system
>
> so, systems will continue to include requirements for case-insensitive
> queries (written by hard-working, well-trained analysts) and we will need to
> implement them either programmatically or through database features -- the
> point is to make it easier (i.e., more efficient and less frustrating) for
> our customers to get their work done while improving the quality of the data
>
> -- mark stock

I agree with you with one small exception. In your case you are using it as a step toward clean data. Something you could have also done with WHERE UPPER(x) = UPPER(y).

But please appreciate in the hands of many, if not most developers, it is a license to be sloppy. And they will use that license and then complain that implicit data-type conversions should be standard too.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)