db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: Case Sensitive
Date Thu, 03 Mar 2005 16:53:06 GMT
Devang wrote:

>My application using derby database as an embedded database. So while
>configuring the database, I found the case sensitivity problem. Let me
>explain further, i.e. my db contains one table  with fields name, address,
>city, street. So I inseted three records with name as "DEVANG" , "devang" ,
>"Devang". When I tried to search name as DEVANG with 'LIKE' , then it
>reterived the single record. It would be nice, if I great an appropriate
>solution for this problem.
>Devang Gandhi
The SQL language is case insensitive (you can enter table names, column 
names, etc in any case), but the SQL execution is generally case 
sensitive. So the database is operating according to spec.

Applications (such as SQL compilers themselves) that want to be case 
insensitive generally internally convert all character data to one case 
and use that case in their SQL queries. For instance, Derby and most 
other SQL databases store their schema data in upper case and search 
their catalogs in upper case. So when a user enters a table name like 
MyTable, Derby internally searches its catalog tables for 'MYTABLE'.

You can use the UPPER or LOWER function in your predicates, e.g.
  WHERE UPPER(name) = UPPER(?)
but this forces a table scan, which is slow. It cannot use an index even 
if one exists on the name column. So this alternative is not preferable. 
If your data is stored in mixed case then there is no choice: you have 
to use UPPER or LOWER function in your predicate and suffer slow 

Jack Klebanoff

View raw message