You know what I hate? Paragraphs of capital (or all lowercase) letters.

The other day a coworker was looking to beautify a large quantity of data spread across some MySQL tables. I created this function to make his life easier:

DELIMITER $
CREATE FUNCTION `f_sentence_case`(strInput TEXT, nMinimumLength INT) RETURNS TEXT
DETERMINISTIC
BEGIN

    DECLARE result TEXT;
    DECLARE LastSpace INT;
    DECLARE NextSpace INT;
    DECLARE NextSlash INT;
    DECLARE Word TEXT;
    DECLARE NewSentence INT;
    DECLARE PreviousCharacter CHAR(1);
    DECLARE TrimmedWord TEXT;
    DECLARE NumberOfSpaces INT;
    DECLARE Swap INT;

    SET strInput := CONCAT(strInput, ' ');
    SET result := '';
    SET LastSpace := 1;
    SET NextSpace := LOCATE(' ', strInput, LastSpace + 1);
    SET NextSlash := LOCATE('/', strInput, LastSpace + 1);

    SET NextSpace := IF(LEAST(NextSlash, NextSpace) = 0, GREATEST(NextSlash, NextSpace), LEAST(NextSlash, NextSpace));

    label1: WHILE NextSpace DO

        SET Word := SUBSTR(strInput, LastSpace, NextSpace - LastSpace);
        SET PreviousCharacter := SUBSTR(strInput, LastSpace - 1, 1);
        SET NewSentence := LastSpace = 1 OR (NewSentence AND PreviousCharacter = ' ') OR PreviousCharacter IN('.', '!', '?');

        SET TrimmedWord := LTRIM(Word);
        SET NumberOfSpaces := LENGTH(Word) - LENGTH(TrimmedWord);
        SET Word := TrimmedWord;

        # Make it lowercase if it is all uppercase
        SET Word := IF(LENGTH(Word) >= nMinimumLength AND Word NOT REGEXP '[0-9]',
            IF(NewSentence,
                CONCAT(UCASE(SUBSTR(Word, 1, 1)), LCASE(SUBSTR(Word, 2, LENGTH(Word) - 1))),
                LCASE(Word)
            ),
            Word);

        SET result := CONCAT(result, REPEAT(' ', NumberOfSpaces), Word);

        SET Swap := LastSpace;
        SET LastSpace := NextSpace;
        SET NextSpace := LOCATE(' ', strInput, Swap + 1);
        SET NextSlash := LOCATE('/', strInput, Swap + 1);
        SET NextSpace := IF(LEAST(NextSlash, NextSpace) = 0, GREATEST(NextSlash, NextSpace), LEAST(NextSlash, NextSpace));

    END WHILE label1;

RETURN result;

END$
DELIMITER ;

What it does

It seems to perform generally as I hoped it would; which is to say that it formats text to be sentence case.

More specifically, it alters all the "words" (a set of non-numeric, non-whitespace characters) that are longer than the specified minimum length.

It changes the words to be all lowercase, unless they happen to be the first word after a punctuation mark (in which case the first character of the word is made uppercase).

Useage

To clean up a field so that it is formatted in sentence case (ignoring all words with less than 3 characters), simply run this query:

UPDATE `table` SET `field` = f_sentence_case(`field`, 3);

Other than that, my only specs for the query were for it to be functional and hopefully not break my brain when I went back to read it later. If anyone has any significant improvements to it, let me know!