15
Oct
09

Working with TitleCase in MySQL

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);
About these ads

2 Responses to “Working with TitleCase in MySQL”


  1. November 26, 2009 at 11:53 pm

    str = ‘BubikolRamios’
    –> ‘Bubikolramios’
    note that there is no space in original str, so this is wrong.

  2. November 27, 2009 at 12:53 am

    replace
    SET s = LCASE( str );
    with
    SET s = s_input;

    I beliwe this does it.


Comments are currently closed.

Tweets

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

del.icio.us motion


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: