How to do case insensitive search with SQL

Problem/Question/Abstract:

Does anyone know how to do a SELECT WHERE statement where it covers all cases. Like, when a user types in 'new york' to search a city field, it finds all occurrences of the string, like 'NEW YORK', 'New York', 'new york', 'NeW yOrk', etc..

Answer:

SELECT * FROM MyTable
WHERE UPPER(City) = 'NEW YORK'

Using UPPER makes it impossible to use an index which may hurt performance.