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)DETERMINISTICBEGIN  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)DETERMINISTICBEGIN   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

umm something like this may work

UPDATE table_name SET `col_name`= CONCAT( UPPER( SUBSTRING( `col_name`, 1, 1 ) ) , LOWER( SUBSTRING( `col_name` FROM 2 ) ) );

If you need to throw custom acronyms and other custom capitalisation patterns into the mix I've generalised hobodave's answer:

DELIMITER |CREATE FUNCTION replaceword( str VARCHAR(128), word VARCHAR(128) )RETURNS VARCHAR(128)DETERMINISTICBEGIN  DECLARE loc INT;  DECLARE punct CHAR(27) DEFAULT ' ()[]{},.-_!@;:?/''"#$%^&*<>';   DECLARE lowerWord VARCHAR(128);  DECLARE lowerStr VARCHAR(128);  IF LENGTH(word) = 0 THEN    RETURN str;  END IF;  SET lowerWord = LOWER(word);  SET lowerStr = LOWER(str);  SET loc = LOCATE(lowerWord, lowerStr, 1);  WHILE loc > 0 DO    IF loc = 1 OR LOCATE(SUBSTRING(str, loc-1, 1), punct) > 0 THEN      IF loc+LENGTH(word) > LENGTH(str) OR LOCATE(SUBSTRING(str, loc+LENGTH(word), 1), punct) > 0 THEN        SET str = INSERT(str,loc,LENGTH(word),word);      END IF;    END IF;    SET loc = LOCATE(lowerWord, lowerStr, loc+LENGTH(word));  END WHILE;  RETURN str;END;|DELIMITER ;DELIMITER | CREATE FUNCTION tcase( str VARCHAR(128) ) RETURNS VARCHAR(128)DETERMINISTICBEGIN   DECLARE c CHAR(1);   DECLARE s VARCHAR(128);   DECLARE i INT DEFAULT 1;   DECLARE bool INT DEFAULT 1;   DECLARE punct CHAR(27) 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 = replaceword(s, 'a');  SET s = replaceword(s, 'an');  SET s = replaceword(s, 'and');  SET s = replaceword(s, 'as');  SET s = replaceword(s, 'at');  SET s = replaceword(s, 'but');  SET s = replaceword(s, 'by');  SET s = replaceword(s, 'for');  SET s = replaceword(s, 'if');  SET s = replaceword(s, 'in');  SET s = replaceword(s, 'n');  SET s = replaceword(s, 'of');  SET s = replaceword(s, 'on');  SET s = replaceword(s, 'or');  SET s = replaceword(s, 'the');  SET s = replaceword(s, 'to');  SET s = replaceword(s, 'via');  SET s = replaceword(s, 'RSS');  SET s = replaceword(s, 'URL');  SET s = replaceword(s, 'PHP');  SET s = replaceword(s, 'SQL');  SET s = replaceword(s, 'OPML');  SET s = replaceword(s, 'DHTML');  SET s = replaceword(s, 'CSV');  SET s = replaceword(s, 'iCal');  SET s = replaceword(s, 'XML');  SET s = replaceword(s, 'PDF');  SET c = SUBSTRING( s, 1, 1 );   IF c >= 'a' AND c <= 'z' THEN        SET s = CONCAT(UCASE(c),SUBSTRING(s,2));   END IF;   RETURN s; END; | DELIMITER ;

Essentially it consists of a case-insensitive word replace function and a function to capitalise the first letter of every word and perform some transforms for specific words.

Hope its helpful to someone.