Steve, I agree with what everyone said here. The data should be stored as the user enters it. I regularly add additional columns to database table that will take a name field, upper-case it and remove all punctuation for sorting purposes. I do this with a database trigger, so applications do not have to do the work.
I realized long ago that there are too many variations in names to try and store it one way, and return it the way the user entered it. The classic example I always use when explaining this to people is the last name teRiele. Notice the lower case first initial. And don't even start talking about the Mc's. You have McDonald, Mcdonald, MacDonald, Mc Donald, Mac Donald, Macdonald - it's insane. You'd be creating a function for each and every name you came across. Realize that if your application is used for communicating with people, especially if you are looking for donations or to do business with those people, that punctuation and proper capitalization must be perfect. Hope this helps. Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Thursday, September 12, 2002 10:08 AM To: Multiple recipients of list ORACLE-L Well said, Philip. I have always used mixed case for the actual data stored in the database and have never encountered a problem, except the index issue you mention, and as you say, function-based indexes cure this problem. If you uppercase the data on storage and rely on initcap to replicate the original capitalizaton scheme, I could see the users getting confused because what they enter isn't always what they are returned. That could be hard to explain to non-computer people. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -----Original Message----- Sent: Wednesday, September 11, 2002 10:23 PM To: Multiple recipients of list ORACLE-L Far be it from me to call you a dinosaur, but it is my personal opinion that unless you have a strong business reason to the contrary, text should be stored in the database in its "natural" form, i.e. mixed case where appropriate. No function that I can even conceive of could handle the variety of address forms/names/etc and output them in a "proper" form. Even if you get it pretty close, there are going to be more than a few exceptions. And there's no excuse not to allow mixed case in the database now that you can use functional indexes. Once again this is just my (possibly humble) opinion from my own personal experience. -- Philip ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 11, 2002 9:38 PM I just had a heated, well perhaps not heated, but certainly adversarial discussion with my manager regarding mixed case text. My position...Keep it out of the database. His position ...same case text looks amateurish when output, and the database should readilly accomodate it. He almost seems to think my objection is related to a shortcoming in Oracle..."I used to do that in Clipper 12 years ago". It is my opinion that if you want to output mixed case data you should use functions to "beautify" text like names and addresses. My biggest objection is that by allowing mixed case text in a table, you are setting up developers (and me) to write queries that don't work. In order to get proper results from a query you have to upper() every single query involving the columns in question. To me this is far more annoying/complicated than calling functions when writing the relatively few reports that require "Proper" text. So am I a dinosaur? Maybe it is because I am not writing the reports that are complicated by my decision to upper() everything while loading. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Philip Douglass INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
