MySQL: How to upper-case words

Posted on 30 September 2010 in
tips programming mysql How to code

I recently needed to clean up a MySQL Table which contained people's names. Upon searching the MySQL commands, I was surprised to find there was no equivalent of PHP's ucfirst or ucwords. There were commands to convert entire strings into upper or lower case, but not just the first letter.

However, I quickly found a simple script to make a word uppercase:

UPDATE table SET field=CONCAT(UCASE(SUBSTRING(field, 1, 1)),LCASE(SUBSTRING(field, 2)));

What if the name needs two capitals?

But then I found an issue; what if someone has a hyphen in their name (like O'Reily) or have a double barreled name (like Smith-John)? This script would make them O'reily and Smith-john (respectively).

I did some more searching and have ended up writing the following two MySQL functions (tested in MySQL 5.1).

Capitalize any given string

This function is a clone of the ucfirst function in PHP.

DROP FUNCTION IF EXISTS UC_FIRST;
CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255)
  RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),SUBSTRING(oldWord, 2));

Capitalize a string based on a delimiter

This function takes a string and a delimiter and capitalizes every words based on breaking the string up using the delimiter.

DROP FUNCTION IF EXISTS UC_DELIMETER;
DELIMITER //
CREATE FUNCTION UC_DELIMETER(oldName VARCHAR(255), delim VARCHAR(1), trimSpaces BOOL) RETURNS VARCHAR(255)
BEGIN
  SET @oldString := oldName;
  SET @newString := "";
  
  tokenLoop: LOOP
    IF trimSpaces THEN SET @oldString := TRIM(BOTH " " FROM @oldString); END IF;
    
    SET @splitPoint := LOCATE(delim, @oldString);
    
    IF @splitPoint = 0 THEN
      SET @newString := CONCAT(@newString, UC_FIRST(@oldString));
      LEAVE tokenLoop;
    END IF;
  
    SET @newString := CONCAT(@newString, UC_FIRST(SUBSTRING(@oldString, 1, @splitPoint)));
    SET @oldString := SUBSTRING(@oldString, @splitPoint+1);
  END LOOP tokenLoop;
  
  RETURN @newString;
END//
DELIMITER ;

A quick demo on capitalizing a name in MySQL

This can then be tested using a line such as this:

SELECT UC_DELIMETER('testing-this-thing', '-', TRUE);

Which should produce

Testing-This-Thing

How should I use this function in MySQL?

The UC_DELIMETER function takes 3 parameters:

String to work on
Delimiter - one character only.
Trim Spaces Boolean. If TRUE then spaces get removed from each end of a string. See Below

The trailing spaces paramenter was added because some people had entered their names with a space after the hyphen in their name, for example:

John- smith

This feature would allow you to run:

SELECT UC_DELIMETER('John- smith', '-', TRUE);

This would produce:

John-Smith

Can anybody suggest any improvements to this? The above simply satisfies my needs, but it'd be great to get this expanded to be more "general purpose".

ThingyMaJig

Connect

Topics