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

Edit

Eureka! Literally my first SQL function. No warranty offered. Back up your data before using. :)

First, define the following function:

DROP FUNCTION IF EXISTS lowerword;
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER |
CREATE FUNCTION lowerword( str VARCHAR(128), word VARCHAR(5) )
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE loc INT;

  SET loc = LOCATE(CONCAT(word,' '), str, 2);
  IF loc > 1 THEN
    WHILE i <= LENGTH (str) AND loc <> 0 DO
      SET str = INSERT(str,loc,LENGTH(word),LCASE(word));
      SET i = loc+LENGTH(word);
      SET loc = LOCATE(CONCAT(word,' '), str, i);
    END WHILE;
  END IF;
  RETURN str;
END;
|
DELIMITER ;

This will lower all occurrences of word in str.

Then define this modified proper function:

DROP FUNCTION IF EXISTS tcase; 
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER | 
CREATE FUNCTION tcase( str VARCHAR(128) ) 
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN 
  DECLARE c CHAR(1); 
  DECLARE s VARCHAR(128); 
  DECLARE i INT DEFAULT 1; 
  DECLARE bool INT DEFAULT 1; 
  DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/'; 
  SET s = LCASE( str ); 
  WHILE i <= LENGTH( str ) DO
    BEGIN 
      SET c = SUBSTRING( s, i, 1 ); 
      IF LOCATE( c, punct ) > 0 THEN 
        SET bool = 1; 
      ELSEIF bool=1 THEN  
        BEGIN 
          IF c >= 'a' AND c <= 'z' THEN  
            BEGIN 
              SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1)); 
              SET bool = 0; 
            END; 
          ELSEIF c >= '0' AND c <= '9' THEN 
            SET bool = 0; 
          END IF; 
        END; 
      END IF; 
      SET i = i+1; 
    END; 
  END WHILE;

  SET s = lowerword(s, 'A');
  SET s = lowerword(s, 'An');
  SET s = lowerword(s, 'And');
  SET s = lowerword(s, 'As');
  SET s = lowerword(s, 'At');
  SET s = lowerword(s, 'But');
  SET s = lowerword(s, 'By');
  SET s = lowerword(s, 'For');
  SET s = lowerword(s, 'If');
  SET s = lowerword(s, 'In');
  SET s = lowerword(s, 'Of');
  SET s = lowerword(s, 'On');
  SET s = lowerword(s, 'Or');
  SET s = lowerword(s, 'The');
  SET s = lowerword(s, 'To');
  SET s = lowerword(s, 'Via');

  RETURN s; 
END; 
| 
DELIMITER ; 

Usage

Verify it works as expected:

SELECT tcase(title) FROM table;

Use it:

UPDATE table SET title = tcase(title);

Source: http://www.artfulsoftware.com/infotree/queries.php?&bw=1070#122

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!

you could do this with concat(), substring(), and length() but I can only see it working for one word. Is there a specific reason why you can't do this in your application's code, instead of mysql?

The definitive case to look for such a function is in the documentation.

Unfortunately, you've got LOWER() and UPPER() functions, but no Title Case. Your best bet would be to declare your own function that splits on spaces, ignores your small words, and does an UPPER on the first character of each remaining word.

The answer is going to depend on what type of data is in the column, i.e. is it people names, place names, book titles, etc. If you are looking for a SQL solution, I would do it in multiple updates, for example:

1. Initial string is: "TO KILL A MOCKINGBIRD"
2. Convert to initial caps: "To Kill A Mockingbird"
3. Convert small words to lower case if they do not start the string: "To Kill a Mockingbird"