Properly use names in SQL

Question

I would like to use the names correctly, which in this case mean:

• The first letter is uppercase.
• The first letter after a space is capitalized (Van Helsing, not Van Helsing)
• The first letter after the dash is uppercase (“Johnson Smith,” not “Johnson Smith”)
• No other letters are capitalized.

The first and last requirements are easily processed:

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

Others are harder. I wrote a 54x REPLACE nested expression (not manually, I used Excel)

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

but I feel that there should be a more elegant and maintainable solution. Any ideas?

If there is a built-in function similar, but not identical to my requirements, which is likely to be fine.

---

: script , , . , , , . , (~ 30%) [-].

Answer 1

, , ... - ( > 2). - , ...

(. Matt Cavanaugh 15 2009 . 3:52 : http://dev.mysql.com/doc/refman/5.1/en/string-functions.html)

Answer 2

" " ( "d" ). "" "'".

, , .

. : "" CakePHP?

Answer 3

, SUBSTRING_INDEX, , , .

Answer 4

You can probably use a user-defined function that will be much easier to reuse.

Answer 5

MySQL doesn't seem to have an INITCAP function, but I found the code here for this:

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 did not write this code, I did not even check it ...