db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Query and uppercases
Date Wed, 26 Apr 2006 16:10:57 GMT
If you are concerned about performance, please note that there is a  
huge difference between these two statements:

1. SELECT * FROM CITY_INFO WHERE UPPER(?) = CITY_NAME

and

2. SELECT * FROM CITY_INFO WHERE ? = UPPER(CITY_NAME)

Query 1 will work if your data is already upper case. You can do an  
index scan of the table very quickly.

If your data is stored in mixed case, you need query 2, and this will  
need to do a table scan because there is no index on the UPPER 
(CITY_NAME) values. [AFAIK there is no way to implement such an index  
on the column. So the index needs to be created with the upper case  
names. If you need mixed case in the database, you might need another  
column just for the upper case index.

Craig

On Apr 26, 2006, at 8:52 AM, Bryan Pendleton wrote:

>> Is there a way to bypass this, so when querying the database it  
>> does not look at uppercases or lowercases. è Detroit = detroit =  
>> dEtRoIt = …
>
> Try using the UPPER function:
>
> select * from city_info where UPPER(city_name) = 'DETROIT';
>
> thanks,
>
> bryan
>
>


Mime
View raw message