0

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?

  • Postgres has a pretty decent full text search to solve exactly that problem. – a_horse_with_no_name Nov 4 '13 at 8:55
2

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.

1

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/

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.