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-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]
