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

Question

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?

Answer 1

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

Answer 2

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