db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Case insensitive indexed search possible?
Date Fri, 02 Nov 2007 13:43:05 GMT
>>>>>>>>>>>> Rick Hillegas wrote (2007-11-02 05:21:09):
> Hi Jon,
> The situation with case-insensitive searching did not change in the 
> latest 10.3 release and no one has volunteered to address this issue in 
> the next feature release, 10.4. If you want to avoid the full table 
> scan, you can store the text in  two columns:  mixed case in one column, 
> and normalized to lowercase in the second column.

Note that in standard SQL, there is no feature that allows for case
insensitive searching, so Rick's proposal with a canonical
representation (e.g. lowercase) of the data is the only portable way
of doing this.

> Regards,
> -Rick
> Jon Miller wrote:
> >Hi all,
> >
> >Does anyone know if there is a way to do a case insensitive indexed 
> >search? I know in the past this wasn't possible, but, I'm wondering if 
> >this might not be fixed in the version that was recently released. 
> >Note, I know you can do a LOWER() to do it. However, I don't want to 
> >do that because I don't want it to do a table scan. Ideally, it would 
> >be possible to make all text searches case insensitive with a single 
> >setting.
> >
> >Jon
> >

Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway

View raw message