db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: case-insensitive searching
Date Tue, 10 Jun 2008 14:10:08 GMT
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:

  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,

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

View raw message