Capitalize names properly in SQL

Question

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 letter 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 [-' ].

Answer 1

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

Answer 2

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: http://stackoverflow.com/questions/3853346/how-to-validate-human-names-in-cakephp/3853820#3853820

Answer 3

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

Answer 4

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