case-insensitive searching

[Geoff hendrey <ge...@yahoo.com>]

Hi Guys,

I was wondering what is being done for case-insensitive comparisons. Also wanted to propose an implementation option, which would be to allow indexes to be created on UPPER or LOWER. As long as I had an index on UPPER or LOWER, I could do this efficiently:

WHERE UPPER(T1.lastname) = UPPER(T2.lastname)

Is that a totally crazy suggestion, to allow indexes to be created on UPPER and LOWER? Maybe this is easy to implement.

-geoff

Re: case-insensitive searching

[Rick Hillegas <Ri...@Sun.COM>]

Hi Geoff,

The community has discussed a couple approaches to case-insensitive 
search. My favorite is DERBY-418 (generated columns), which pretty much 
aligns with what you are proposing. DERBY-481 already has a functional 
spec attached to it. With generated columns, you would be able to index 
a case-insensitive column like so:

CREATE TABLE employee
(
  employeeID               int,
  name                        varchar( 50 ),
  caseInsensitiveName  GENERATED ALWAYS( UPPER( name ) )
)
;
CREATE INDEX caseInsensitiveEmployeeName ON employee( caseInsensitiveName )

Then the WHERE clause of your query would be pretty simple:

WHERE t1.caseInsensitiveName = t2.caseInsensitiveName

Hope this helps,
-Rick

Geoff hendrey wrote:
> Hi Guys,
>
> I was wondering what is being done for case-insensitive comparisons. 
> Also wanted to propose an implementation option, which would be to 
> allow indexes to be created on UPPER or LOWER. As long as I had an 
> index on UPPER or LOWER, I could do this efficiently:
>
> WHERE UPPER(T1.lastname) = UPPER(T2.lastname)
>
> Is that a totally crazy suggestion, to allow indexes to be created on 
> UPPER and LOWER? Maybe this is easy to implement.
>
> -geoff
>

Re: case-insensitive searching

[Mark Thornton <mt...@optrak.co.uk>]

Geoff hendrey wrote:
> Hi Guys,
>
> I was wondering what is being done for case-insensitive comparisons. 
> Also wanted to propose an implementation option, which would be to 
> allow indexes to be created on UPPER or LOWER. As long as I had an 
> index on UPPER or LOWER, I could do this efficiently:
>
> WHERE UPPER(T1.lastname) = UPPER(T2.lastname)
>
> Is that a totally crazy suggestion, to allow indexes to be created on 
> UPPER and LOWER? Maybe this is easy to implement.
>
> -geoff
>
Proper internationalization would require indices based on a Collator as 
defined by java.text.Collator. In general the default Collator for a 
locale is a better basis for sorting than either upper or lower case.

Mark Thornton

Re: case-insensitive searching

[Knut Anders Hatlen <Kn...@Sun.COM>]

Geoff hendrey <ge...@yahoo.com> writes:

> Hi Guys,
>
> I was wondering what is being done for case-insensitive
> comparisons. Also wanted to propose an implementation option, which
> would be to allow indexes to be created on UPPER or LOWER. As long as
> I had an index on UPPER or LOWER, I could do this efficiently:
>
> WHERE UPPER(T1.lastname) = UPPER(T2.lastname)
>
> Is that a totally crazy suggestion, to allow indexes to be created on
> UPPER and LOWER? Maybe this is easy to implement.

Hi Geoff,

It's not a crazy suggestion at all. We already have some similar feature
requests in the bug tracker. I think DERBY-455 is more or less the same
idea, but you may also take a look at some other JIRAs:

https://issues.apache.org/jira/browse/DERBY-455
https://issues.apache.org/jira/browse/DERBY-481
https://issues.apache.org/jira/browse/DERBY-1748

DERBY-481 (implement SQL generated columns) has had some activity
recently, but I don't know if anyone is planning to implement it.

-- 
Knut Anders