Devang wrote:
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.Hi
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.
Thanx
Devang Gandhi
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 performance.
Jack Klebanoff
