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?
Best Answer
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.