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".