You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Jon Miller <je...@uchicago.edu> on 2007/11/01 23:52:41 UTC
Case insensitive indexed search possible?
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
Re: Case insensitive indexed search possible?
Posted by Daniel John Debrunner <dj...@apache.org>.
Daniel John Debrunner wrote:
> Bernt M. Johnsen wrote:
>>>>>>>>>>>>>> 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.
See DERBY-1748 for details on an easy way to implement this.
https://issues.apache.org/jira/browse/DERBY-1748
Dan.
Re: Case insensitive indexed search possible?
Posted by Daniel John Debrunner <dj...@apache.org>.
Bernt M. Johnsen wrote:
>>>>>>>>>>>>> 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.
SQL does allow implementation specified collations and thus provides the
framework to perform case-insensitive searching, though specifying (ie.
DDL or create database) how will be database specific.
With the language based ordering added to 10.3 Derby has the basic
blocks to implement case-insensitive searching. At the database level
(applies to all user character strings) would be the easiest, then at
the schema level (all data within that schema), then at the data type level.
The database level would require adding support for a new collation, and
then ensuring that collation leads to the correct java.text.Collator
being set up. I think it might be as easy as using the current code and
setting the strength of the current collator to be something other than
primary(?).
Dan.
Re: Case insensitive indexed search possible?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> 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
Re: Case insensitive indexed search possible?
Posted by Rick Hillegas <Ri...@Sun.COM>.
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.
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
>