Re: Case insensitive queries

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1070049775.995089_at_yasure...
| 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)
|

not just a step toward cleaning up data -- there are still times when there are legitimate reasons why the data of interest can be stored in different case (i.e., case neither adds not detracts value from the data)

re: WHERE UPPER(x) = UPPER(y) -- make sure to add a function based index

when setting the Case Insensitive Query item property in Forms, it generates something like:

(colname = 'the search value as typed in'

or colname like 'Th%'
or colname like 'TH%'
or colname like 'th%'
or colname like 'tH%'

(going from memory on syntax) which will allow the optimizer to use a normal index

yeah, functionality gets abused -- just like cheap memory has gotten abused by commercial software developers -- but don't take away my kodachrome just 'cause someone else might misuse the functionality!

btw: is there a missing grin after the 'implicit data-type conversion' comment?

• mcs