Case Sensitivity when doing Select

I have been using SQL Server but am in the process of switching over to Firebird. With SQL Server I was able to do a select clause
WHERE COL1 = "w"

and it would match with a value of UPPER CASE "W" in the COL1 field.

With Firebird, it appears as though the SELECT clause is case sensitive. I know that there are way of getting around this issue by using the UPPER function, but I wondering if that is the only way. Is it possible to do a select with Firebird which is case insensitive (without resorting to making everything uppercase for example)?

I've also read a bit about the COLLATE option when creating a table. I understand that this has an impact on case sensitivity as it relates to the use of ORDER BY in a query. Does it also have an impact on the case sensitivity of a WHERE clause in a select query?

Depending on how much data you're going to have in your database, using an UPPER function on the left side of an equal sign can cause performance degredation. If you're getting acceptable performance then don't worry about it. But if your performance starts to degrade as your data grows, then the function on the left is causing the database engine to ignore any index you may have on COL1 and it's doing a table scan. The database engine has to cast every COL1 to uppercase and check for a match to see if the two values are equal.

A couple of other options to think about:

1. If you're running this on a Windows box, use a character set that supports case-insensitive collation like SQL Server does.
http://www.brookstonesystems.com/ibCollate3.html

2. A common way to overcome case-sensitivity / indexing problems is to create a shadow column of any indexed character fields. If you were going to index LastName then you would create another column called LastName_Upper and a trigger that would set this field to an uppercase version of LastName. You would then index LastName_Upper and compare it to an uppercase version of whatever the user enters. In other words, you move the UPPER function to the right side of the equal sign. This allows an index to be used. Read the bottom of the following link:
http://www.destructor.de/firebird/trigger.htm

3. Sybase gives away ASE Express for free on Linux. The upside is that the syntax between Sybase and SQL Server is very similar since they were the same product until version 4.2. If you're a SQL Server / TSQL guru, it should be very familiar. It also supports case-insensitive collation on *nix boxes. The downside is that the free version is limited to 1 CPU, 5 gig of data and 2 gig of RAM.
http://www.sybase.com/linuxpromo

The containing keyword works case-insensitiv (at least in most character sets):

SELECT *
FROM table
WHERE col1 CONTAINING 'w';

Btw: character literals are denoted by single quotes not double quotes as in your example

Thanks so much for responding to my question (especially dcaillouet and shammat). That is exactly the type of information I needed. It will likely save me a lot of wasted time.

I will be having a very large indexed database and it hadn't occurred to me that having the UPPER on the left side of the equation (where it would be converting all of the records in the indexed field to upper case) would cause performance degredation and would basically ignore the index.

I'm not sure which method I will employ in the end, but I have enough information now to approach the problem intelligently.

Thanks again!

The only open-source database that I know allows you to index computed columns is Postgresql. This may be a feature of other databases as well because I know commercial databases like SQL Server allow you to do indexed views. You could create a view with a computed column and then index it. But Postgresql would allow you to create a table index on the function upper(LastName) just like it would allow you to create an index on LastName.

From the documentation:

The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses. Multiple fields can be specified if the index method supports multicolumn indexes.

An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index.

PS - Don't take my word for this. New features are being added to databases all the time and some open-source databases may have recently added this feature and I'm just not aware of it. Postgresql was the only one I was certain of.