Blog

You would think that the world's most popular open source database, as MySQL like to call itself, would have a function for making items title case (where the first letter of every word is capitalized). Sadly it doesn't.

I had a request from a client yesterday to scrub some data in the users table of an application. This collection of users has been edited since the late '90s and includes people who have used caps lock, people who undervalue their name and have used no caps, and a number of others who only enter an initial for the first name and make it lowercase.

Since I was only asked to work on the first name field, there wasn't much call to worry about usage cases in last names like McKenzie, O'Connel, and the client when asked about what they wanted to do with first names that had two capital letters, like for instance, LaFonda, said just to change them all.

In many ways MySQL is superior to FileMaker - these include speed, the footprint on server, however MySQL does not have a Proper() function to allow title casing of values.

Over at Artful Software I found a prepared statement that worked particularly well:

DROP FUNCTION IF EXISTS proper;
SET GLOBAL  log_bin_trust_function_creators=TRUE;
DELIMITER |
CREATE FUNCTION proper( str VARCHAR(128) )
RETURNS VARCHAR(128)
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   -- Jesse Palmer's correction from < to <= for last char
    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;
RETURN s;
END;
|
DELIMITER ;

There was a great way to do this. I loaded this from the command line into MySQL after having first chosen a database to store the procedure against. You can look at this as working almost the same way in FileMaker's Custom Functions.

Here's how it's called once it's defined:

SELECT proper(First) FROM customers;

And here's how to use it to alter the column it's looking at.

UPDATE customers SET First = proper(First);