Should I store all the text in my database in uppercase or in lowercase?

Question

Which one do you prefer to store text in your database? The original casing of the data, or some kind of normalization. Also, should I enforce this with triggers? or should I preprocess input data with client code?

I ask you, because I'm not sure about if there is any difference, besides additional processing time to display data (capitalization of names, for example).

Answer 1

I wouldn't worry about it, just format it how you need it to be used on output.

Answer 2

Store in original case, unless there is a reason to do otherwise. The time spent to upper or lower case the data is likely very small, and you never know when you'll want the real, original data back in the future.

Answer 3

If you need to do lots of text field searches (strcol = "aBc"), you could consider normalizing. In some db systems like postgres, indexes aren't used with the ILIKE operator, although the text search specific indexes probably handle this better.

Answer 4

1. In the absence of a compelling need to have your data changed, don't change your data.
2. In the absence of a compelling reason to have your database indices case sensitive, use case insensitive in your database.
3. Spend time worrying about what a user sees and wants. This will guide you to your answer.

Answer 5

It obviously depends on what you'll need to do with the data later.

But if you're asking because you're interested in speeding up lookups later (e.g. searching by name, city, etc.), you may want to create different indexes, so that those would be used instead for the lookups:

create table case_test (
  id integer,
  name varchar2(30));

create index ucasename on case_test(upper(name));

A query like this sample would use the case-insensitive index for selection criteria but return the name with casing as input originally:

select * from case_test where upper(name) like 'TUCK%';

Answer 6

Slightly off topic but... NEVER trust client side formatting of ANYTHING.

Answer 7

Store in the actual case the user entered.

As a user, if i like my name as cHriS, I enter it that way and I like it that way. I don't like the application change it just for some technical reasons

If you want to optimize search, use separate indexes, don't change the data.