Hi, I'm mostly repeating what others have answered before, just trying to emphasize and put things together in one email:

a) If you use an uppercase collation or an expression index, then an uppercased search can be indexed.

b) LIKE does not use an index.
The only case where LIKE can use an index, is where you use LIKE <constant> and <constant> starts with a fixed value. If you use a parameter rather than a constant, or the constant starts with a wildcard (like your examples do), then no index can be used.

So your two examples will both be slow when you have lots (millions) of records in your table. An alternative to using "where upper(field) like '%UPPERCASE LOOK FOR%'" is "where field containing 'LOOK FOR'". Unlike LIKE, CONTAINING is case insensitive. However, it still cannot use any index.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of bwc3068
Sent: 27. juni 2009 01:05
To: firebird-support@yahoogroups.com
Subject: [firebird-support] case insensitive "where" clause

Hi--

A simple question on making my where clauses case insensitive with FB 2.nn.

is there an easier way then setting up the SQL like the below?

select * from table
where
upper(field) like '%UPPERCASE LOOK FOR%'
order by
upper(field)

is there a speed issue? that is...will that resolve as fast as:

select * from table
where
field like '%LOOK FOR%'
order by
field

thanks again!
regards
kelly