ignoring letter case in search

[Anon]

in doing something like:

select * from table where text like '%word%';

how could i make it so it isn't case sensitive? (so searching for "word" would find "word" or "Word" etc.)

Thanks,
Chief
http://www.crudpot.net

[[deleted]]

The standard method is probably also the slowest method, but it'll work on most (if not all) databases; simply make every thing upper or lowercase, and compare that.

SELECT *
FROM table
WHERE UPPER(col1) LIKE UPPER('%WoRd%');

Note: this is double-slow because the db can't use indexes, and all the data has to be converted to uppercase before it can be compared.

Real RDBMS's like Informix can create indexes on processed data.
That means that you can store the data in normal case, but create an index on the uppsercase version of the data, without storing the uppercase version anywhere in the table.