Search code examples
mysqlphpmyadmintitle-case

Is there a simple way to convert MySQL data into Title Case?

I have a MySQL table where all the data in one column was entered in UPPERCASE, but I need to convert in to Title Case, with recognition of "small words" akin to the Daring Fireball Title Case script.

I found this excellent solution for transforming strings to lowercase, but the Title Case function seems to have been left out of my version of MySQL. Is there an elegant way to do this?

Solution

  • Woo! I'm not handy with SQL at all; Here's the method that worked for me:

    1. Export the table as a text file in .sql format.
    2. Open the file in Textmate (which I already had handy).
    3. Select the rows with UPPERCASE data.
    4. Choose "Convert" from the "Text" menu, and select "to Titlecase".

    5. Find and replace each instance of:

      INSERT INTO `Table` (`Col1`, `Col2`, `Etc`, ...) VALUES

      with the correct lowercase values.

    6. Import the table back into the database.
    7. Use UPDATE table SET colname=LOWER(colname); to reset lowercase values for the columns that should be lowercase.

    The reason I didn't try using Textmate before was that I couldn't figure out how to convert a single column to Title Case without ruining the other data, but this method seems to work. Thanks for your guidance and support!