Uppercasing all character data in a database

Question

Uppercasing all character data in a database

Andrew Terwiel-314315

SSC Journeyman

Points: 94
More actions
July 8, 2010 at 10:24 pm

#141026

Can anyone give me reasons why not to uppercase all character data in a database?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

My main reason is because it's a one-way operation. There's no way to go back to the previous case. If there is any possibility of requiring the data in the original case, I'd push back.

You can create views that will convert everything to UPPER() on the fly, but the performance of this may be poor. You could get around that by storing the UPPER and the original values separately, but this will take up more space.

Can your boss give a good reason to capitalise all data irreversibly? At the end of the day though, it's his database, and his decision.

happycat59 One Orange Chip Points: 29384 More actions · Answer 2

For me, the reason not to make everything upper case is exactly the same reason that everything we write is a mixture of upper and lower case - it is much easier for us to read and comprehend. Converting everything to upper case will reduce your users ability to comprehend the data they are using.

If your SQL installation is case sensitive and this is some form of change to ensure that the SQL written against the database is not hindered by this, then redundantly store a column that is upper case. This could be managed within the database using a calculated column and if needed, an index on that column.

If your SQL installation is not case sensitive, then there is no technical reason to make everything upper case.

Andrew Terwiel-314315 SSC Journeyman Points: 94 More actions · Answer 3

Thanks for your reply. We have an application created by a third party vendor that has a relationship with a varchar field in a table that we own to a varchar field in a table they own, instead of using the int key field in the tables. If the case of the data in our table doesn't match the case in their table, then no match is found. Basically it's a coding error on their part. My boss is not a database expert and believes the easiest solution is to uppercase all our data. I think the third party vendor should fix their code.

happycat59 One Orange Chip Points: 29384 More actions · Answer 4

So, at least one of the columns/databases uses a case sensitive collation. This does not mean that your comparisons (e.g. join conditions) need to be case sensitive as well. Why not just use a case insensitive comparison

e.g. WHERE VenderTable.Column = YourTable.Column Collate Latin1_General_CI_AS

This works for SQL 2000. I don't think it works with SQL 7, though

steve block SSCertifiable Points: 7698 More actions · Answer 5

I'd check into converting the database to Case Insensitive (if it won't break the application). Otherwise, I would add the UPPER (or lower) function to the appropriate fields when querying.

When I see all uppercase, it seems to Shout at me and therefore is not very friendly. It also goes back to old mainframe days and therefore looks very archaic. If it is only a single field that is used as a key it might not be too bad.

Steve