Capitalize names properly in SQL

I would like to capitalize names properly, which in this case means:

• The first letter is capitalized.
• The first letter after a space is capitalized ('Van Helsing', not 'Van helsing')
• The first let开发者_如何转开发ter after a dash is capitalized ('Johnson-Smith', not 'Johnson-smith')
• No other letters are capitalized.

The first and last requirements are easily handled:

CONCAT(LEFT(name, 1), LOWER(RIGHT(name, LENGTH(name) - 1)))

The others are harder. I've written a 54-times-nested REPLACE statement (not by hand of course, I used Excel)

REPLACE(REPLACE(REPLACE(REPLACE(...,' b',' B'),'-b','-B'),' a',' A'),'-a','-A')

but I feel like there must be a more elegant and maintainable solution. Any ideas?

If there's a built-in function that is similar but not identical to my requirements that would probably be fine.

Edit: This script will only run on names that have lost their capitalization already, so there's no danger in mis-handling obscure names. Handling apostrophes like spaces and dashes would be nice, though. A glance through the current data shows that many (~30%) of the names have at least one of [-' ].

What about "Van den Berg" (which has a lower case 'd'). What about "McDonald" or "O'Reilly".

It's generally considered a bad idea to over-validate people's names, because there's always going to be a legitimate name which breaks your validation.

See also my answer to a previous similar question here: How to "Validate" Human Names in CakePHP?

The code frustratedwithforms posted does not work correctly...it correctly capitalizes the first and last words in the string and deletes anything in between (if there are > 2). Someone posted a corrected version at the URL he posted...

(See Matt Cavanaugh's post on May 15 2009 3:52pm at: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html)

It won't be pretty, but you can used SUBSTRING_INDEX to find the first space and dash, and conditionally capitalise whatever succeeds it.

You could probably use an User Defined Function, that would be much easier to reuse.

It looks like MySQL doesn't have an INITCAP function, but I found code for one here:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

And the code too:

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap` $$
CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END $$
DELIMITER ;

Disclaimer: I didn't write this code, I haven't even tested it...