Query and uppercases

[Catteeuw Peter <Pe...@realsoftwaregroup.com>]

Hello,

 

could anyone help me with the following problem:

 

When I do a SELECT query and I search for e.g. "Detroit" but the data in
the derby db is "detroit" then the query has no results.

Is there a way to bypass this, so when querying the database it does not
look at uppercases or lowercases. ==> Detroit = detroit = dEtRoIt = ...

 

Help would be appreciated.

 

Thx,

Peter

Re: Query and uppercases

[Rick Hillegas <Ri...@Sun.COM>]

Although this won't help anyone solve the immediate problem immediately, 
this issue would be addressed by DERBY-455 (expression indexes).

Regards,
-Rick

Craig L Russell wrote:

> 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
>>
>>
>

Re: Query and uppercases

[Craig L Russell <Cr...@Sun.COM>]

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
>
>

Re: Query and uppercases

[Bryan Pendleton <bp...@amberpoint.com>]

> 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