Handling case-insensitive queries with case-sensitive data

Question

What are some practices for designing a table, or queries, when it comes to case-sensitive data and case-insensitive queries?

For example, I have a Products table that holds household goods, and presumably because I intend to display this data on a client, I would like to have the original names.

If one of the products is called "Vacuum Cleaner" and someone's doing a search through the database for vacuums, I wouldn't want a term like "vacuum" to not find this particular vacuum.

Currently I am using Postgres so I could probably get away with the ILIKE operator, but if a database system didn't support this kind of operator, what would be some alternatives?

Answer 1

SQL text and string data has a concept called "collation". Collations define how character sets compare and sort. Almost every database out there has the concept of "case-sensitive collation" vs. "case-insensitive collation". The collation used for comparison, search and sort can be changed on-the-fly (at a significant runtime cost usually) on a per-query basis. More often the collation is chose during development and defined at the database/table/column level and then the engine will use the chosen collation to do search/comparison/sorting of strings/text.

The specifics of how you define the collation to use will depend on your platform of choice.

Answer 2

This is database vendor dependent.

For postgresql use database type citext (case-insensitive text). Avoid using ILIKE, as this is not recommended by the vendor.

http://www.postgresql.org/docs/9.1/static/citext.html

http://www.depesz.com/2008/08/10/waiting-for-84-case-insensitive-text-citext/

Answer 3

I'm not sure if I 100% understand your question. This is how I understand your question:

How can I match "vacuum" to "Vacuum Cleaner" inside my database?

To match something like that you can use the % wildcard within the LIKE function. Basically bottom line you can do that by sending the following query.

SELECT * FROM `products` WHERE LOWER(`name`) LIKE LOWER('%vacuums%');

The above query should would then of course return Vacuum Cleaner as one of the results.

Extra

Here are a few links where you can read more into detail about the wildcards.

• http://www.sqlexamples.info/PHP/mysql_like_wildcards.htm
• http://www.sqlexamples.info/PHP/mysql_rlike.htm