Archive for October, 2009

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);
Advertisements
03
Oct
09

Singletrack

02
Oct
09

Lasso Developers Conference 2009

200910021312.jpg I’m here at the Lasso Developers Conference in Amsterdam Netherlands. This is the 6th Annual LDC and the first European LDC. This year is the first year that the conference is doing a split track and supporting some of the newer conference formats such at lightning talks, in addition to the formats from other years including round tables, workshops, and sessions.

Yesterday I spoke on MVC patterns in Lasso, atBegin and URL handling. I’ll post that on slideshare soon and put a link up here. Sunday is the next day that I have a session and I also have a lightning talk on a new framework we’ve been working on called SingleTrack. More on that later.

The climate here in Amsterdam is cool, but the coding is heated. Both days so far I’ve been sitting next to Marc Vos, who has his home here in the Netherlands. We had a great presentation this morning from Rachel Guthrie on managing client expectations, and I picked up some great ideas about building and managing expectations from the start of the project and how to avoid scope creep, something that everyone has problems with. If you don’t, then you aren’t selling yourself well enough.

The spaces conference center is located near the west end of Amsterdam near the downtown center, it’s also near the Annie Frank house where Anne Frank hid during the german occupation. Basically from what it appears, the center is three buildings next to each other and the middle one is hollowed out. Here’s another shot from the inside.

200910021329.jpg

One of the things that’s amazing about spaces is the variety of spaces inside the venue. There’s a small juice bar on the platformed area that serves coffee, smoothies, and various juices and such. There’s a series of booths with power outlets, and deep cushions, which are backed by mirrors and gives them a sense of depth. Over near the entrance is a posh pit with sectioned areas and more power outlets. This is what it looks like:

200910021337.jpg

Wireless is present through out and the whole floor is cabled with network cables as well as power that are available through the access panels in many places.




Tweets

del.icio.us motion