CODEWITHSUNDEEP
mysqlreplace
Charles
Charles

Reputation: 11479

Capitalize names properly in SQL

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

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

Upvotes: 3

Views: 6253

Answers (5)

Jimmy Mensinger
Jimmy Mensinger

Reputation: 36

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)

Upvotes: 2

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27496

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

Upvotes: 1

David Mårtensson
David Mårtensson

Reputation: 7600

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

Upvotes: 1

Spudley
Spudley

Reputation: 168695

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?

Upvotes: 4

Marcelo Cantos
Marcelo Cantos

Reputation: 185862

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

Upvotes: 1

Related Questions