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
