Re: How to convert strings to 'proper case' ?
Thanks Rhino, Johan, Melvin, In my application I don't have stuff like A.b. Mcdonald etc. But descriptions like SUGARS AND SUGAR CONFECTIONERY. This data is loaded as uppercase from a CSV textfile into a MySQL table. Currently I use the MS Access function StrConv(Description,3) to set propercase, giving: Sugars And Sugar Confectionery, followed by a script for stopwords like: SET @from := ' And ', @to := ' and '; UPDATE products SET Description = REPLACE(Description, @from, @to); to get Sugars and Sugar Confectionery. The reason for my question was to avoid using MS Access. I will certainly take a closer look at your suggestions ! Regards, Cor - Original Message - From: Rhino [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 2:42 PM Subject: Re: How to convert strings to 'proper case' ? I'd be surprised if things actually turned out to be quite as simple as you describe. For example, let's say that your column actually contained book titles or names of people. Would you really want to see any of the following in your column: - A Diplomatic History Of The Un? (more likely: A Diplomatic History of the UN) - A.b. Mcdonald? (more likely: A. B. McDonald) - The Life And Times Of King George Iii? (more likely: The Life and Times of King George III) In any case, I don't think a simple SQL UPDATE will do what you want to do, at least not very easily. You'll almost certainly want some real programming statements to do the string manipulation that you need. You haven't said whether your data is already in tables or whether you are planning to load the data into new tables. You also haven't said whether the data is in all upper case, all lower case or in some form of mixed case. If the data is not already in tables, I'd be inclined to change the case of the data with a scripting language that was appropriate for your operating system and then load the corrected data into the tables. For instance, on Linux, I might write a bash shell script to reformat the data, which is presumably sitting in a flat file somewhere, then load the reformatted data into the tables. This gives you the option of choosing from several different scripting languages, some of which you may already know fluently. That could save you a lot of time. If the data is already in tables, you could unload it to a flat file, fix it with a shell script, and then reload it to the database. Or, you could write a User Defined Function (UDF) or Stored Procedure (SP) in order to update the existing values. Then you could call the procedure or function to do the necessary work at any time you found data with the wrong case. If you write an SP, you could pass the table name and column name to the procedure. Then, the procedure could do a loop that operated on every value in that column of the table. For each row, it could read the existing value, create a revised value using string manipulation techniques, then update the current value with the revised value. Creating the revised value would likely be the only tricky part and even that might not be very hard if it really were only necessary to convert the first letter of each word to a capital. If the code actually had to handle more complex cases like the ones I put at the beginning of this note, the code would be more complicated; it might even be impossible if the language you were using for the SP or UDF didn't have many string manipulation techniques. In that case, you might need to choose a different language or you could go back to unloading the data from the database, manipulating it outside MySQL, and then reloading it. That's all I'm going to say for the moment but if you decide to try a UDF or SP and can state what programming languages you are willing to use for the code - and whether the conversion is really as simple as capitalizing just the first letters of the words - I might be able to give you more specific suggestions. Unfortunately, I don't have a current version of MySQL and can't really install one so I can only talk hypothetically, based on UDFs and SPs that I've written in DB2 and on what I've seen in the MySQL manuals. I can't actually write you a simple UDF or SP for MySQL that would do at least the basic parts of the conversion you want. Maybe someone else on this mailing list has an example that you could have which is actually known to work in MySQL. Otherwise, you might only have hypothetical guidelines and manual articles to guide you as you try to write your UDF or SP. That can be time-consuming if you've never done any coding like that before. But it could be fun too if you are in the right frame of mind! -- Rhino - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 1:41 AM Subject: How to convert strings to 'proper case' ? Hi List, I want to convert strings to proper
Re: How to convert strings to 'proper case' ?
Hi Cor, Don't know if that function exists in MySQL... If you by any chance is using PHP you can do it by using ucfirst(str) But I quote the User Comment at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html from Tom O'Malley: quote Posted by Tom O'Malley on April 18 2006 1:16am An example of how to make the first letter in a string uppercase - analogous to UCFIRST SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName /quote /Johan C.R.Vegelin wrote: Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
I'd be surprised if things actually turned out to be quite as simple as you describe. For example, let's say that your column actually contained book titles or names of people. Would you really want to see any of the following in your column: - A Diplomatic History Of The Un? (more likely: A Diplomatic History of the UN) - A.b. Mcdonald? (more likely: A. B. McDonald) - The Life And Times Of King George Iii? (more likely: The Life and Times of King George III) In any case, I don't think a simple SQL UPDATE will do what you want to do, at least not very easily. You'll almost certainly want some real programming statements to do the string manipulation that you need. You haven't said whether your data is already in tables or whether you are planning to load the data into new tables. You also haven't said whether the data is in all upper case, all lower case or in some form of mixed case. If the data is not already in tables, I'd be inclined to change the case of the data with a scripting language that was appropriate for your operating system and then load the corrected data into the tables. For instance, on Linux, I might write a bash shell script to reformat the data, which is presumably sitting in a flat file somewhere, then load the reformatted data into the tables. This gives you the option of choosing from several different scripting languages, some of which you may already know fluently. That could save you a lot of time. If the data is already in tables, you could unload it to a flat file, fix it with a shell script, and then reload it to the database. Or, you could write a User Defined Function (UDF) or Stored Procedure (SP) in order to update the existing values. Then you could call the procedure or function to do the necessary work at any time you found data with the wrong case. If you write an SP, you could pass the table name and column name to the procedure. Then, the procedure could do a loop that operated on every value in that column of the table. For each row, it could read the existing value, create a revised value using string manipulation techniques, then update the current value with the revised value. Creating the revised value would likely be the only tricky part and even that might not be very hard if it really were only necessary to convert the first letter of each word to a capital. If the code actually had to handle more complex cases like the ones I put at the beginning of this note, the code would be more complicated; it might even be impossible if the language you were using for the SP or UDF didn't have many string manipulation techniques. In that case, you might need to choose a different language or you could go back to unloading the data from the database, manipulating it outside MySQL, and then reloading it. That's all I'm going to say for the moment but if you decide to try a UDF or SP and can state what programming languages you are willing to use for the code - and whether the conversion is really as simple as capitalizing just the first letters of the words - I might be able to give you more specific suggestions. Unfortunately, I don't have a current version of MySQL and can't really install one so I can only talk hypothetically, based on UDFs and SPs that I've written in DB2 and on what I've seen in the MySQL manuals. I can't actually write you a simple UDF or SP for MySQL that would do at least the basic parts of the conversion you want. Maybe someone else on this mailing list has an example that you could have which is actually known to work in MySQL. Otherwise, you might only have hypothetical guidelines and manual articles to guide you as you try to write your UDF or SP. That can be time-consuming if you've never done any coding like that before. But it could be fun too if you are in the right frame of mind! -- Rhino - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 1:41 AM Subject: How to convert strings to 'proper case' ? Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
My God! Rhino, that was a very long and very good answer!! Impressive!! /Johan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to convert strings to 'proper case' ?
Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor
