Re: Case insensitive queries

On 24 Nov 2003 09:48:34 -0800, ed.prochak_at_magicinterface.com (Ed prochak) wrote:

>Thomas Kellerer <spam_eater_at_gmx.net> wrote in message news:<bprhh8$nsr$1_at_svr7.m-online.net>...
>> Hans Forbrich schrieb:
>>
>> > JB wrote:
>> >
>> >>How can one set the Oracle database in case insensitive query mode?
>> >>How can this be done globally(always) as well as per query and per
>> >>session? All three please!
>> >>
>> >
>> > (Seems to me the requirement
>> > for case-insensitivity would be a generated by programmer laziness or
>> > insufficient user training - not uncommon these days.)
>>
>> Let's assume a search application with searches for contacts, companies,
>> addresses, products,... You name it.
>>
>> I think it's a valid requirement, that the user does not need to know how a
>> name, city, etc has been written, and I have been coding around this
>> shortcoming of Oracle for several years now.
>> It's good to hear that Oracle finally can do what the competitors could do
>> for years.
>>
>> Just my .02€
>>
>> Thomas
>
>
>And I've programmed data cleanup that had to deal with source data
>that was case insensitive. Addresses can be especially troubling.
>Dupont and DuPont are NOT the same street in some locations. The
>search issue is bigger than case sensitivity, just consider puncuation
>like names with contractions. Removing case sensitivity doesn't help.
>
>So it's not a limition of the ORACLE DB.
>
>It's a limitation of some other databases that cannot deal with the
>true nature of the data. (When all your data is in CAPS, there is no
>case issue.) And sometimes a limit of application developers to deal
>with it properly also.

In a case like that, I'd use Oracle Text's CONTAINS() operator.

...WHERE CONTAINS(last_name, 'de la hoya') would match 'De La Hoya' etc. You could also set it up so that punctuation characters (e.g. dashes) are treated like a space, a significant character, or just ignored so the words are run together into one. That's settable independently for each punctuation character.

John

--
Photo gallery: http://www.pbase.com/john_russell/